return rows that arent distinct

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

General SQL Server Forums

New to SQL Server Programming

return rows that arent distinct

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2008-05-05 : 09:48:12

I am trying to create a query that will find all the records that have the same value multiple times in the a column called phonenumber.

How do i return disticnt records having count greater than 1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-05 : 09:52:54

[code]select phonenumber, count(*)
from table
group by phonenumber
having count(*)>1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 10:47:11

And if you want to retrieve all details use the above query as a subquery
SELECT * from table where phonenumber in (select phonenumber, count(*)
from table
group by phonenumber
having count(*)>1)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-05 : 11:02:37

quote:
Originally posted by visakh16

And if you want to retrieve all details use the above query as a subquery
SELECT * from table where phonenumber in (select phonenumber ~~count(*)~~
from table
group by phonenumber
having count(*)>1)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vivekroberts
Starting Member

2 Posts

Posted - 2008-05-08 : 04:01:17

select *
from YourTable
where phonenumber not in (select distinct phonenumber from YourTable group by phonenumber having count(phonenumber)=1 )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-08 : 04:23:09

quote:
Originally posted by vivekroberts

select *
from YourTable
where phonenumber not in (select distinct phonenumber from YourTable group by phonenumber having count(phonenumber)=1 )

You dont need to use DISTINCT as you use GROUP BY Clause

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources