Oracle – How to Select the First Row of Each Group

greatest-n-per-grouporacle

I have a table like this:

 ID |  Val   |  Kind
----------------------
 1  |  1337  |   2
 2  |  1337  |   1
 3  |   3    |   4
 4  |   3    |   4

I want to make a SELECT that will return just the first row for each Val, ordering by Kind.

Sample output:

 ID |  Val   |  Kind
----------------------
 2  |  1337  |   1
 3  |   3    |   4

How can I build this query?

Best Answer

This solution also uses keep, but val and kind can also be simply calculated for each group without a subquery:

select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val;

ID |  VAL | KIND
-: | ---: | ---:
 3 |    3 |    4
 2 | 1337 |    1

dbfiddle here

KEEP…FIRST and KEEP…LAST are an Oracle-specific feature of aggregates — you can read about then here in the Oracle docs, or on ORACLE_BASE:

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence

Related Question