CODEWITHSUNDEEP
sqlsql-server
Robin clave
Robin clave

Reputation: 638

Select only duplicate records based on few columns

i have a table with 3 columns. It is having duplicate on column 2 and 3. I need to select pnly the duplicate rows (with all the 3 columns). How to do that? pls help me.

var1 var2 var3

a a a
b a a
c a a
d b b
e c c

The above is the table structure. It has 3 columsn var1, var2, var3. Based on column var2 and var3 only, we have to assume it is duplicate records. We should not consider var1 while finding the duplicate. Sorry about the alignment.

I have used the below query to get all the rows with / without duplicate but with the rank to identify the duplicate records. But i could not get only the duplicate records.

select var1,var2,var3,ROW_NUMBER() over(PARTITION BY var2,var3 order by var2,var3) as rnk from vart

Apart from this, how i can get the maximum rank records amoung the duplicate entries ? Thanks.

Upvotes: 2

Views: 12809

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

If you want all the rows that have duplicates you can use count(*) over()

select var1, var2, var3
from (
      select var1,
             var2,
             var3,
             count(*) over(partition by var2, var3) as dc
      from YourTable
     ) as T
where dc > 1

Result:

var1 var2 var3
---- ---- ----
a    a    a
b    a    a
c    a    a

If you want all duplicates but one use row_number() over() instead. 

select var1, var2, var3
from (
      select var1,
             var2,
             var3,
             row_number() over(partition by var2, var3 order by var1) as rn
      from YourTable
     ) as T
where rn > 1

Result:

var1 var2 var3
---- ---- ----
b    a    a
c    a    a

Upvotes: 10

cairnz
cairnz

Reputation: 3957

Using your query, you can expand it with a CTE and do what you want

with cte as
(
select var1,var2,var3,ROW_NUMBER() over(PARTITION BY var2,var3 order by var2,var3) as rnk from vart
)
select * from cte where rnk > 1

This will list all the "duplicate" rows.

You can delete the dupes using the cte too:

with cte as
(
select ROW_NUMBER() over(PARTITION BY var2,var3 order by var2,var3) as rnk from vart
)
delete from cte where rnk > 1

Upvotes: 0

Chetter Hummin
Chetter Hummin

Reputation: 6837

select var1,var2,var3 from vart where (var2+'#'+var3) in 
(
   select var2+'#'+var3 from vart group by var2,var3 having count(var1) > 1
)

Upvotes: 0

hago
hago

Reputation: 1710

what is your primary key? if var1 is, try:

select var1
from vart
group by var2+var3
having count(*)>1

if it's not, try:

select * from vart where pk in
(
    select pk
    from vart
    group by var2+var3
    having count(*)>1
)

Upvotes: 0

Related Questions