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?

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.

StackExchange Database Administrators Q#6368, answer score: 80