Select the first row for each group in MySQL?

Question

I have a small table with around 700 records. I want to pick the first record of each group.

The query is the following

SELECT *
FROM release_image ri
ORDER BY ri.release_id, ri.position DESC

and the result set, looks like the following image. As you can see, i want to pick the first of each group, it should be:

release_id  image_id   position
------------------------------------
1           1809      3
2           1010      1
3           2240      2
4           2245      2
...

How do i go about that?

Answer 1

You can GROUP BY and pick the MAX position.

SELECT ri.*
FROM (
    SELECT ri.release_id, MAX(ri.position) AS position
    FROM release_image ri
    GROUP BY ri.release_id
) ri_max
INNER JOIN release_image ri ON ri_max.release_id = ri.release_id
    AND ri_max.position = ri.position