Xaedblade Xaedblade - 1 year ago 75
SQL Question

Return id of row that has duplicate data

I am needing to get the row id of rows which have duplicate

Select Name from table1 group by Name having count(1) > 1


ID | Name | ClientID
01 | John | 01
02 | Sam | 01
03 | Sue | 01
04 | John | 02
05 | John | 01

the only problem is that it will only return the Name and not the ID of the column as I have multiple clients on the same table and I don't want to count the names from the other clients as duplicates.

So is there a way to find duplicate data in a table and return the ids of the rows so that I can then use those id's in another query?

Answer I went with modified

Thank you every one for the answers I went with this one which is a modification of the one I marked as the answer.

select t1.*
from (select count(*) over (partition by entityname) as cnt ,t1.*
from table1 t1 where ClientID = 1
) t1
where cnt > 1 and ClientID = 1 order by cnt;

Answer Source

Use a window function:

select t1.*
from (select t1.*, count(*) over (partition by name) as cnt
      from table1 t1
     ) t1
where cnt > 1;

The count(*) over (partition by name) counts the number of rows for each name. However, it does this by appending the count on each row, not by reducing the number of rows. That's the information you need for selecting the rows.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download