How find duplicates when I have several columns?

boson · October 15th, 2004, 08:27 AM

Hello,

How can I check if i have duplicates with a SQL-question when I have a table like this. Lets say the columns are integer and there are no unique index set on them and there are no primery key.

col1 col2
1 1
1 1

What I would like is something like this question does, but here it is only one column to check for duplicates.

select id, count(id) as Sum
from tabell
group by id
having count(id) > 1

So the result from the two columns above would be something like this
col1 col2 Sum
1 1 2

Thanks in advance
// Boson

Justin Cave · October 15th, 2004, 08:44 AM

Just add rows to the SELECT and GROUP BY clauses

Code:

SELECT col1, col2, COUNT(*) as Sum
  FROM table
 GROUP BY col1, col2
HAVING count(*) > 1

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC