Search code examples
sqlgreatest-n-per-group

Select Only the row with smallest id

suppose I have the following table X:

Id  Type  Name
1   1   Jane
2   2   Mary
3   3   Rose
4   4   Rachel
5   4   Darren
6   4   Jay

What will be the select statement to generate:

Id  Type  Name
1   1   Jane
2   2   Mary
3   3   Rose
4   4   Rachel

that only the first row for each type will be selected? Appreciate a lot.

Solution

  • A simple method is to use a correlated subquery:

    select t.*
from t
where t.id = (select min(t2.id) from t t2 where t2.type = t.type);

    Here is the DEMO for the same.