How to select the first row of each group?

Question

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?

Answer 1

Use a common table expression (CTE) and a windowing/ranking/partitioning function like ROW_NUMBER.

This query will create an in-memory table called ORDERED and add an additional column of rn which is a sequence of numbers from 1 to N. The PARTITION BY indicates it should restart at 1 every time the value of Val changes and we want to order rows by the smallest value of Kind.

WITH ORDERED AS
(
SELECT
    ID
,   Val
,   kind
,   ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Kind ASC) AS rn
FROM
    mytable
)
SELECT
    ID
,   Val
,   Kind
FROM
    ORDERED
WHERE
    rn = 1;

The above approach should work with any RDBMS that has implemented the ROW_NUMBER() function. Oracle has some elegant functionality as expressed in mik's answer that will generally yield better performance than this answer.

Answer 2

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

Answer 3

bilinkc's solution works fine, but I thought I'd toss mine out as well. It has the same cost, but might be faster (or slower, I haven't tested it). The difference is that it uses the First_Value instead of Row_Number. Since we are only interested in the first value, in my mind it is more straightforward.

SELECT ID, Val, Kind FROM
(
   SELECT First_Value(ID) OVER (PARTITION BY Val ORDER BY Kind) First, ID, Val, Kind 
   FROM mytable
)
WHERE ID = First;

Test Data.

--drop table mytable;
create table mytable (ID Number(5) Primary Key, Val Number(5), Kind Number(5));

insert into mytable values (1,1337,2);
insert into mytable values (2,1337,1);
insert into mytable values (3,3,4);
insert into mytable values (4,3,4);

If you prefer, here is the CTE equivalent.

WITH FirstIDentified AS (
   SELECT First_Value(ID) OVER (PARTITION BY Val ORDER BY Kind) First, ID, Val, Kind 
   FROM mytable
   )
SELECT ID, Val, Kind FROM FirstIdentified
WHERE ID = First;

Answer 4

You can use keep to select an id from each group:

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

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

dbfiddle here

Answer 5

select * 
from (
    select t1.*, ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Val desc) as seqnum 
    from tablename t1
) 
where seqnum=1;

Answer 6

SELECT MIN(MyTable01.Id) as Id,
       MyTable01.Val     as Val,
       MyTable01.Kind    as Kind 
  FROM MyTable MyTable01,                         
       (SELECT Val,MIN(Kind) as Kind
          FROM MyTable                   
      GROUP BY Val) MyTableGroup
WHERE MyTable01.Val  = MyTableGroup.Val
  AND MyTable01.Kind = MyTableGroup.Kind
GROUP BY MyTable01.Val,MyTable01.Kind
ORDER BY Id;