| Author |
 Topic  |
|
ivra
Starting Member
Philippines
18 Posts |
 Posted - 03/04/2010 : 05:55:40
|
Hi,
Our database has alot of duplicates phone number and we want to see those records. Is it possible to display the duplicates records in order asc or by descd depends on how many they appears?
ID | Phone
1 | 16454
2 | 5485
3 | 16454
4 | 11542
5 | 16454
6 | 5485
The query should return
ID | Phone
1 | 16454
3 | 16454
5 | 16454
2 | 5485
6 | 5485
4 | 11542
Is this possible?
Thanks |
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 03/04/2010 : 06:18:39
|
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 03/04/2010 : 06:29:52
|
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail |
Edited by - madhivanan on 03/04/2010 06:31:28 |
|
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 03/04/2010 : 06:55:04
|
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail
I would not use any JOIN on very simple queries.
Do you like following sentence very much?
"cause performance issues" |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 03/04/2010 : 06:58:31
|
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail
Your approach is good..
could i ask,u have used here in select t2.phone instead select t1.phone?
hope you h ave valid reason for this..
could you please temme what is that? |
|
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 03/04/2010 : 07:23:44
|
quote:
Originally posted by haroon2k9
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail
Your approach is good..
could i ask,u have used here in select t2.phone instead select t1.phone?
hope you h ave valid reason for this..
could you please temme what is that?
No Valid Reason , You can use either t1.phone or t2.phone in select list
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 03/04/2010 : 07:27:50
|
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail
I would not use any JOIN on very simple queries.
Do you like following sentence very much?
"cause performance issues"
You always test your queries with small set of data
Test your solution with large set of data
It doesn't matter whether query is simple or complex but effecient one is important
Why do you think you wont use JOIN for this?
How do you think your solution is best?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 03/04/2010 : 07:30:06
|
Yes friends go for another madhi vs. ms65g.
I like watching that 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 03/04/2010 : 07:31:22
|
quote:
Originally posted by webfred
Yes friends go for another madhi vs. ms65g.
I like watching that
No, you're never too old to Yak'n'Roll if you're too young to die.
Yes He/she rarely listen
Madhivanan
Failing to plan is Planning to fail |
|
|
|
bhuvnesh.dogra
Starting Member
India
22 Posts |
Posted - 03/04/2010 : 07:38:05
|
can anybody tell the ouput of "SELECT COUNT(*) FROM @temp T2 WHERE T1.Phn = T2.Phn"
---Bhuvnesh-----
While 1=1(learning Sql...)
|
|
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 03/04/2010 : 07:40:38
|
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail
I would not use any JOIN on very simple queries.
Do you like following sentence very much?
"cause performance issues"
You always test your queries with small set of data
Test your solution with large set of data
It doesn't matter whether query is simple or complex but effecient one is important
Why do you think you wont use JOIN for this?
How do you think your solution is best?
Madhivanan
Failing to plan is Planning to fail
I would not use join. Is not any problem?
And when I have said my query is the best?
And would you like that I do not post any more suggestions and solutions in this site? You are a moderator in Performance site! Wow |
Edited by - ms65g on 03/04/2010 07:46:19 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 03/04/2010 : 07:41:31
|
quote:
Originally posted by bhuvnesh.dogra
can anybody tell the ouput of "SELECT COUNT(*) FROM @temp T2 WHERE T1.Phn = T2.Phn"
---Bhuvnesh-----
While 1=1(learning Sql...)
Count the data for each Phn matches with Phn column of the outer table
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 03/04/2010 : 07:47:32
|
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail
I would not use any JOIN on very simple queries.
Do you like following sentence very much?
"cause performance issues"
You always test your queries with small set of data
Test your solution with large set of data
It doesn't matter whether query is simple or complex but effecient one is important
Why do you think you wont use JOIN for this?
How do you think your solution is best?
Madhivanan
Failing to plan is Planning to fail
I would not use join. Is not any problem?
And when I have said my query is the best?
And would you like that I do not post any more suggestions and solutions in this site? You are a moderator in Performance site! Wow
I am not saying you should not post a solution
If anyone points that your query can be modified efeeciently you need to accept it instead of counter arguing
I am simply pointing you that the queries you are posting can be modified effeciently. You need to think differently when you try to post the similar solution. This way anyone can learn and start writing queries in a better way
When anyone points out you, why are you not accepting it?
Also, what is this to do with being a moderator on the other site?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 03/04/2010 : 07:55:05
|
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Dont use SELECT in the ORDER BY. It may cause performance issues.
Instead use this approach
select t1.id,t2.phone from table_name as t1 inner join
(
select phone,count(*) as counting from table_name
group by phone
) as t2
on t1.phone=t2.phone
order by counting desc,id asc
Madhivanan
Failing to plan is Planning to fail
I would not use any JOIN on very simple queries.
Do you like following sentence very much?
"cause performance issues"
You always test your queries with small set of data
Test your solution with large set of data
It doesn't matter whether query is simple or complex but effecient one is important
Why do you think you wont use JOIN for this?
How do you think your solution is best?
Madhivanan
Failing to plan is Planning to fail
I would not use join. Is not any problem?
And when I have said my query is the best?
And would you like that I do not post any more suggestions and solutions in this site? You are a moderator in Performance site! Wow
I am not saying you should not post a solution
If anyone points that your query can be modified efeeciently you need to accept it instead of counter arguing
I am simply pointing you that the queries you are posting can be modified effeciently. You need to think differently when you try to post the similar solution. This way anyone can learn and start writing queries in a better way
When anyone points out you, why are you not accepting it?
Also, what is this to do with being a moderator on the other site?
Madhivanan
Failing to plan is Planning to fail
Do you point out anybody else me in this site?
If do, what I did not see!
If you politely point and advice me I will accept it.
Your sentences are command not suggestion!
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 03/04/2010 : 07:56:53
|
<<
If you politely point and advice me I will accept it.
>>
Next time I will do it 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 03/04/2010 : 07:59:58
|
quote:
Originally posted by madhivanan
<<
If you politely point and advice me I will accept it.
>>
Next time I will do it
Madhivanan
Failing to plan is Planning to fail
I hope it. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 03/04/2010 : 08:06:25
|
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
<<
If you politely point and advice me I will accept it.
>>
Next time I will do it
Madhivanan
Failing to plan is Planning to fail
I hope it.
Dont think that I am de-motivating you
I am simply advising you that you can turn your queries to effecient ones.
Madhivanan
Failing to plan is Planning to fail |
|
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 03/04/2010 : 12:40:55
|
quote:
Originally posted by madhivanan
quote:
Originally posted by ms65g
quote:
Originally posted by madhivanan
<<
If you politely point and advice me I will accept it.
>>
Next time I will do it
Madhivanan
Failing to plan is Planning to fail
I hope it.
Dont think that I am de-motivating you
I am simply advising you that you can turn your queries to effecient ones.
Madhivanan
Failing to plan is Planning to fail
Then you are a free public SQL Server consultant/adviser. But I thought you have a special allergy against me.
OK, I was thinking in wrong.
|
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 03/04/2010 : 13:40:33
|
quote:
I am not saying you should not post a solution
If anyone points that your query can be modified efeeciently you need to accept it instead of counter arguing
I am simply pointing you that the queries you are posting can be modified effeciently. You need to think differently when you try to post the similar solution. This way anyone can learn and start writing queries in a better way
When anyone points out you, why are you not accepting it?
Also, what is this to do with being a moderator on the other site?
Madhivanan
Failing to plan is Planning to fail
My advice is just lets not suggest him anything.Let him post whatever he likes.Its up to the mods to decide about him.
PBUH |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 03/04/2010 : 13:46:08
|
quote:
Originally posted by Idera
quote:
I am not saying you should not post a solution
If anyone points that your query can be modified efeeciently you need to accept it instead of counter arguing
I am simply pointing you that the queries you are posting can be modified effeciently. You need to think differently when you try to post the similar solution. This way anyone can learn and start writing queries in a better way
When anyone points out you, why are you not accepting it?
Also, what is this to do with being a moderator on the other site?
Madhivanan
Failing to plan is Planning to fail
My advice is just lets not suggest him anything.Let him post whatever he likes.Its up to the mods to decide about him.
PBUH
Its not first instance that he's reacted in this way
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 03/04/2010 : 13:56:53
|
quote:
Originally posted by visakh16
quote:
Originally posted by Idera
quote:
I am not saying you should not post a solution
If anyone points that your query can be modified efeeciently you need to accept it instead of counter arguing
I am simply pointing you that the queries you are posting can be modified effeciently. You need to think differently when you try to post the similar solution. This way anyone can learn and start writing queries in a better way
When anyone points out you, why are you not accepting it?
Also, what is this to do with being a moderator on the other site?
Madhivanan
Failing to plan is Planning to fail
My advice is just lets not suggest him anything.Let him post whatever he likes.Its up to the mods to decide about him.
PBUH
Its not first instance that he's reacted in this way
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Seems like another whitefang in making even he used to point out quotes from Joe Celko's book the way ms65g does.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125549
To bad ms65g english is not as good as whitefang.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124203&whichpage=4
I liked the custom title of whitefang.It was Enterprise-Level Plonker Who's Not Wrong
Maybe Ms65g to deserves something like that.
PBUH |
|
|
|
Topic |
|
order by duplicate counts
