CODEWITHSUNDEEP
sql-serverdatabaseduplicates
if_i_could
if_i_could

Reputation: 11

Query for searching duplicates rows on multiple columns

Need to find the rows with duplicates data in a multiple columns

hID     sid    lid    aid    did     shid    soid         date
------------------------------------------------------------------
0       123    jkn    rbr    tm      asd     hjk       10/11/2008
1       563    kjh    jkh    dfsd    we      hjk       11/12/2010
2       131    jklj   jkjk   adsd    vcv     asd       01/01/2012
3       656    hjkh   hjkh   dasda   fas     fads      03/26/2012
4       123    jkn    rbr    tm      asd     hjk       09/09/2012

Should be able to return like dis one..

hID     sid    lid    aid    did     shid    soid         date
------------------------------------------------------------------
0       123    jkn    rbr    tm      asd     hjk       10/11/2008
4       123    jkn    rbr    tm      asd     hjk       09/09/2012

Any ideas? thanks in advance!

Upvotes: 1

Views: 6296

Answers (4)

user2287500
user2287500

Reputation: 1

;with tempTable as
(
select *, row_number() over(partition by sid, lid, aid, did, shid, soid order by hid asc )  as rowid
from #table)
select *
from tempTable
where rowid>1

Upvotes: 0

vladr
vladr

Reputation: 66661

The query below is much more repetitive than @TimSchmelter's solution, but yields a measurable performance advantage for large tables with relatively few duplicates if you happen to have an index on sid, lid, aid, did, shid, soid.

SELECT t.* FROM t INNER JOIN (
  SELECT sid, lid, aid, did, shid, soid, COUNT(1) AS count FROM t
  GROUP BY sid, lid, aid, did, shid, soid HAVING COUNT(1) > 1
) tt ON t.sid=tt.sid AND t.lid=tt.lid AND t.aid=tt.aid AND t.did=tt.did AND t.shid=tt.shid AND t.soid=tt.soid;

Upvotes: 2

mvp
mvp

Reputation: 116048

This works in all SQL servers I tried (SQLFiddle):

SELECT * FROM t
WHERE (sid,lid,aid,did,shid,soid) IN (
    SELECT sid,lid,aid,did,shid,soid
    FROM t
    Group By 1,2,3,4,5,6
    HAVING count(1) > 1
)

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460018

You can GROUP BY multiple columns. But since you want to find all duplicates you can use EXISTS:

SELECT
   hID, sid, lid, aid, did, shid, soid, date
FROM TableName t1
WHERE EXISTS
(
   SELECT 1 FROM TableName t2
   WHERE t2.hid <> t1.hid
   AND  t2.sid  = t1.sid   
   AND  t2.lid  = t1.lid    
   AND  t2.aid  = t1.aid   
   AND  t2.did  = t2.did
   AND  t2.shid = t2.shid   
   AND  t2.soid = t2.soid
)

Upvotes: 1

Related Questions