Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 order by duplicate counts

Author  Topic 

ivra
Starting Member

18 Posts
Posted - 2010-03-04 : 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 - 2010-03-04 : 06:18:39
[code]SELECT * FROM table_name T1
ORDER BY (SELECT COUNT(*) FROM table_name T2 WHERE T1.Phone = T2.Phone) DESC, ID ASC[/code]
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-04 : 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"
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts
Posted - 2010-03-04 : 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?
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-03-04 : 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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

bhuvnesh.dogra
Starting Member

22 Posts
Posted - 2010-03-04 : 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...)
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-04 : 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!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-04 : 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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-04 : 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.
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-04 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-04 : 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/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-04 : 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
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site