Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.

https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

SQL Query DISTINCT

20-05-2003 03:01PM

#1

flyz

Closed Accounts Posts: 536 ✭✭✭

Join Date: January 2002

Posts: 451

Can anyone tell me how to select all duplicate entries from a database table?

that is the opposite to this :

SELECT DISTINCT col_name FROM TableName

0

Comments

#2 20-05-2003 03:04PM

DaithiSurfer

Closed Accounts Posts: 843 ✭✭✭

Join Date: December 2002

Posts: 779

select count(*), blah, blah2, blah etc
from tables
where blah
having count(*) > 1
group by blah, blah2, blah etc

can't remember if the group by goes before or after the having.
try both and see

0
#3 20-05-2003 03:08PM

flyz

Closed Accounts Posts: 536 ✭✭✭

Join Date: January 2002

Posts: 451

the following works :

select count(*), blah
from tables
group by blah
having count(*) > 1

cheers

0
#4 20-05-2003 07:00PM

DeVore

Registered Users, Registered Users 2 Posts: 32,387 ✭✭✭✭

Join Date: February 1998

Posts: 29920

Thats a nice way to do it.

Another (and I suspect less efficient... certainly less elegant) way of doing it would be to join the table to itself, you could join on field blah and where table1.id <>table2.id

Come to think about it that might in certain circumstances be more efficient as its using indices rather then counts.

PM me if you need the exact SQL. My brains fried right now.

DeV.

0