Remove duplicates in Select query based on one column

Question

I want to select without duplicate ids and keep row '5d' and not '5e' in select statement.

table

id | name
1  | a
2  | b
3  | c
5  | d
5  | e

I tried:

SELECT id, name 
FROM table t
INNER JOIN (SELECT DISTINCT id FROM table) t2 ON t.id = t2.id

Answer 1

You can also use ROW_NUMBER():

SELECT id, name
FROM (
    SELECT id, name, ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) rn 
    FROM mytable
) x
WHERE rn = 1

This will retain the record that has the smallest name (so '5d' will come before '5e'). With this technique, you can also use a sort criteria on another column that the one where duplicates exists (which an aggregate query with MIN() cannot do). Also, queries using window functions usually perform better than the equivalent aggregate query.

Answer 2

For the given example an aggregation using min() would work.

SELECT id,
       min(name) name
       FROM table
       GROUP BY id;

Answer 3

If you want to keep the row with the smallest name then you can use not exists:

select t.* from tablename t
where not exists (
  select 1 from tablename
  where id = t.id and name < t.name
)