# How to select the first row of each group?

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?

-
why 3|3|4 and not 4|3|4 - what is the tie-break or do you not care? –  Jack Douglas Sep 30 '11 at 18:46
@JackDouglas Actually I have a `ORDER BY ID DESC`, but that is not relevant for the question. In this example I do not care. –  BrunoLM Sep 30 '11 at 21:54

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;
``````
-

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;
``````
-
+1 but I just thought it worth emphasising that your answer and billinkc's are not logically the same unless `id` is unique. –  Jack Douglas Sep 30 '11 at 19:01
@Jack Douglas - True, I assumed that. –  Leigh Riffel Oct 1 '11 at 0:02

Assuming `id` is unique (like Leigh's answer), yet another alternative is:

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

ID                     VAL                    KIND
---------------------- ---------------------- ----------------------
2                      1337                   1
3                      3                      4
``````
-

This solution uses 'keep' which was used before in many solutions, val and kind can also be simply calculated for each group.

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

``````select distinct
first_value(id)   over (partition by val order by kind, id rows between unbounded preceding and unbounded following) id
, first_value(val)  over (partition by val order by kind, id rows between unbounded preceding and unbounded following) val
, first_value(kind) over (partition by val order by kind, id rows between unbounded preceding and unbounded following) kind
from mytable
``````

Uses analytical function first_value to take the value with smallest kind (order by kind) from all values (rows ...) in the group with the same val (partition by val). This is calculated for each row, so distinct is needed to get only one value per group.

-
``````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;
``````
-
That will be a lot less efficient than the other answers due to the fact that two scans over MyTable are needed. –  a_horse_with_no_name Nov 22 '13 at 9:49

try this and hope it solved your problem.

``````SELECT * FROM
(SELECT * FROM YOUR_TABLE_NAME ORDER BY Kind ASC) TABLE_ALIAS
GROUP BY Val order by ID
``````
-
The question is for Oracle. This kind of on-standard query will only work in MySQL and only under certain settings and only in some versions. –  ypercube yesterday