CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user311509
user311509

Reputation: 2866

Pick a Single Row Out of Each Set of Duplicates

Mock up table:

enter image description here

I need to eliminate duplicate records from the first table. In case we have duplicate address_num such as 100, we will go with the row of AddressType "Home".

Please note that address_num do not come in duplicates of 3 or more; they either appear once or twice in a table. For example, address_num 200 appeared once. Therefore, we just grabbed it as is.

ID column is the primary key.

The second table with the right check mark is the results table. How can I write an oracle SQL query that prints out the second table?

Upvotes: 1

Views: 47

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146499

Select only those rows with the lowest Id within each set of Dupes.

 Select * From MockUpTable m
 Where Id = 
     (Select Min(id)
      From MockUpTable
      Where address_num = m.address_num)

Upvotes: 0

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Something like:

SELECT id, address_num, ...
FROM (
    SELECT id, address_num, ...
         , row_number() over (partition by address_num
                              order by addresstype) as rn
    FROM T
) AS X
WHERE rn = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can do this with row_number() and an intelligent use of order by:

select t.*
from (select t.*,
             row_number() over (partition by address_num
                                order by (case when AddressType = 'Home' then 1 else 0 end) desc
                               ) as seqnum
      from table t
     ) t
where seqnum = 1;

Upvotes: 2

Related Questions