CODEWITHSUNDEEP
sql
Dahab
Dahab

Reputation: 514

SQL select duplicated row in a table

I am trying to select all duplicates from db table:

This is my try:

dev=> select * from import_mapper where source_id=12772;
  id   |     entity      | source_id | local_id | venue_id 
-------+-----------------+-----------+----------+----------
 41002 | appointment_mbo |     12772 |    67101 |       50
 46046 | appointment_mbo |     12772 |    67101 |       50
 46192 | appointment_mbo |     12772 |    87814 |      149
 48557 | appointment_mbo |     12772 |    90100 |       66
 49310 | appointment_mbo |     12772 |    90750 |       65
(5 rows)

my expected result should be only first 2 row:

  id   |     entity      | source_id | local_id | venue_id 
-------+-----------------+-----------+----------+----------
 41002 | appointment_mbo |     12772 |    67101 |       50
 46046 | appointment_mbo |     12772 |    67101 |       50

Note: for sure regardless of ID field

Upvotes: 0

Views: 59

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Most databases support the ANSI standard window functions. So, you can try:

select t.*
from (select t.*,
             count(*) over (partition by entity, source_id, local_id, venue_id) as cnt 
      from t
     ) t
where cnt >= 2;

Note that this version will even work intuitively if some of the columns have NULL values.

Upvotes: 0

Arjan
Arjan

Reputation: 9874

SELECT im1.*
FROM import_mapper im1
INNER JOIN import_mapper im2
USING (source_id, local_id, venue_id, entity)
WHERE im1.id != im2.id

That should give you all duplicates (leaving the id out of the equation).

Upvotes: 0

bpachev
bpachev

Reputation: 2212

You can do a group by on all of the columns except id, and then use having to filter out those with count equal to one. This solution also gets the number of duplicates.

select id, entity, source_id, local_id, venue_id, count(*) n from
   import_mapper group by entity, source_id, local_id, venue_id having n > 1

Upvotes: 0

Siyual
Siyual

Reputation: 16917

You can do this with a WHERE EXISTS clause over each of the fields you are considering in your uniqueness check:

Select  *
From    import_mapper   m1
Where Exists
(
    Select  *
    From    import_mapper   m2
    Where   m1.id != m2.id
    And     m1.entity = m2.entity
    And     m1.source_id = m2.source_id
    And     m1.local_id = m2.local_id
    And     m1.venue_id = m2.venue_id
)

Upvotes: 1

Related Questions