SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 order by duplicate counts
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

ivra
Starting Member

Philippines
18 Posts

Posted - 03/04/2010 :  05:55:40  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/04/2010 :  06:29:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 03/04/2010 :  06:55:04  Show Profile  Reply with Quote
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"
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 03/04/2010 :  06:58:31  Show Profile  Reply with Quote
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?
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 03/04/2010 :  07:23:44  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/04/2010 :  07:27:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 03/04/2010 :  07:30:06  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/04/2010 :  07:31:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

bhuvnesh.dogra
Starting Member

India
22 Posts

Posted - 03/04/2010 :  07:38:05  Show Profile  Reply with Quote
can anybody tell the ouput of "SELECT COUNT(*) FROM @temp T2 WHERE T1.Phn = T2.Phn"

---Bhuvnesh-----
While 1=1(learning Sql...)
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 03/04/2010 :  07:40:38  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/04/2010 :  07:41:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/04/2010 :  07:47:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 03/04/2010 :  07:55:05  Show Profile  Reply with Quote
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!


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/04/2010 :  07:56:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 03/04/2010 :  07:59:58  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/04/2010 :  08:06:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 03/04/2010 :  12:40:55  Show Profile  Reply with Quote
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.

Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 03/04/2010 :  13:40:33  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/04/2010 :  13:46:08  Show Profile  Reply with Quote
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/

Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 03/04/2010 :  13:56:53  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000