Marco Marco - 1 year ago 75
MySQL Question

Select the first row for each group in MySQL?

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

The query is the following

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?

enter image description here

Answer Source

You can GROUP BY and pick the MAX position.

    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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download