CODEWITHSUNDEEP
oracle-databaseselectdistinct
Zolt
Zolt

Reputation: 2811

How to select unique values for a column where the IDs are different?

I have a select statement that is returning an ID column and value column. The values in the ID column are distinct, but there are duplicates in the value column.

Here is an example: 

    ID    |    Value
-----------------------
    1     |     Bob
    2     |     Jane
    3     |     Bob
    4     |     Mike
    5     |     Suzy
    6     |     Mike

As you can see there are 4 unique name values but 6 records returned because the IDs are all different. This normally should not be done, but this is just example data (not really using names) and for what I need to do I need to remove records with duplicate names.

I was trying to use the lead or lag function but couldn't get it to remove the duplicates.

Any ideas on how to modify a straight forward select statement to get both columns but with only unique names?

Upvotes: 2

Views: 5719

Answers (2)

krokodilko
krokodilko

Reputation: 36107

This deletes recorde with duplicate names, but leaves the first record with lowest id.

DELETE FROM table t
WHERE EXISTS (
   SELECT null FROM table t1
   WHERE t.name = t1.name AND t.id < t1.id
)

If you only want to select records, then

SELECT * FROM table t
WHERE NOT EXISTS(
   SELECT null FROM table t1
   WHERE t.name = t1.name AND t.id < t1.id
)

Upvotes: 3

Lukas Eder
Lukas Eder

Reputation: 220842

Try:

SELECT MIN(ID), Value
FROM table
GROUP BY Value

Alternative:

SELECT DISTINCT MIN(ID) OVER (PARTITION BY Value), Value
FROM table

Upvotes: 0

Related Questions