SQLTeam.com | Weblogs | Forums

Find Duplicate Rows


#1

How can I find duplicate rows based on 2 columns ?

If data is below
col1 col2 col3 col4 col5 ...
val1 val2 val3 val4
val1 val2 val5 val6
val1 val3 val7 val8
val0 val3 val7 val8

Result based on col1 and col2 is

col1 col2 col3 col4 col5 ...
val1 val2 val3 val4
val1 val2 val5 val6


#2

Perhaps this:

select b.*
  from (select col1
              ,col2
          from yourtable
         group by col1
                 ,col2
         having count(*)>=2
       ) as a
       inner join yourtable as b
               on b.col1=a.col1
              and b.col2=a.col2
;

#3

You can can also use COUNT() OVER () to finD duplicates.
e.g.

WITH CTE AS
(
SELECT col1 ,col2 ,col3 , col4
,COUNT(Col1) OVER(PARTITION BY col1, col2) AS DUP_COUNT

FROM TABLE_NAME
)
SELECT *
FROM CTE
WHERE DUP_COUNT > 1