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?

How is "first" defined in this context? Is it the record with the highest value for the `position` column? — Asaph, Oct 01 '16 at 04:51
exactly, the record with the highest position is the first record — Marco, Oct 01 '16 at 14:06
Possible duplicate of [How to select the first row for each group in MySQL?](https://stackoverflow.com/questions/2739474/how-to-select-the-first-row-for-each-group-in-mysql) — Evan Carroll, Oct 20 '17 at 19:51

score 5 · Accepted Answer · edited Dec 10 '21 at 23:22

5

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

edited Dec 10 '21 at 23:22

Tony

9,672
3
47
75

answered Oct 01 '16 at 04:51

2

`MIN(ri.position)` should be `MAX(ri.position)` as per OP's comment on the question: "... the record with the highest position is the first record". – Asaph Oct 01 '16 at 19:50
@Terminus, Thank you for your query. Could you also please explain why the answer by Ruslan Zhomir may or may not be correct? – Surbhi Jain Feb 11 '19 at 05:01
@SurbhiJain search online for "mysql ONLY_FULL_GROUP_BY" Also, this problem is a greatest-n-per-group question. A lot of good explanations here on SO about that. – Feb 12 '19 at 15:44

Ruslan Zhomir · Answer 2 · 2022-09-20T21:05:38.997

0

May be like this:

SELECT release_id, image_id, MAX(position)
FROM release_image ri 
GROUP BY release_id

MySQL allow you to add columns to select clause, that are not in the group by clause and are not using aggregate functions.

Update

My above example is for sure WRONG.

As pointed out in the comment below by PeteH32, MAX(position) indeed has no effect on which row GROUP BY uses. As stated at https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html :

the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses.

It means, that the answer with joins is the only correct one

edited Sep 20 '22 at 21:05

answered Oct 01 '16 at 05:04

Ruslan Zhomir

842
9
27

1

I do not think "MAX(position)" has any affect on which row GROUP BY uses. For example, although the column "MAX(position)" may have largest value for position, the value in column "image_id" could be for any unrelated position. (The values for the two columns end up not being from same row, since GROUP BY is being used.) – PeteH32 Dec 08 '17 at 16:18
@PeteH32, Are you sure that the value of image_id is not the value corresponding to MAX(position), I ran the above mentioned SQL command, but I am getting the right value for image_id. – Surbhi Jain Feb 11 '19 at 04:53
I saw the problem at work, which was with a pretty large data set. It's been over a year so I don't remember details anymore, but my testing confirmed that the above type of SQL query would not work in my situation. The resultset sometimes contained columns values that were obviously from different rows (i.e. the MAX column value was not from same row as the image_id column value). Unfortunately the documentation for MySQL seems to avoid this common use-case. It's examples are close, but slightly different: https://mariadb.com/kb/en/library/max/ – PeteH32 Feb 18 '19 at 21:29

score -1 · Answer 3 · answered Oct 01 '16 at 04:51

-1

Try

SELECT *
FROM release_image ri
GROUP BY ri.release_id, ri.position DESC;

answered Oct 01 '16 at 04:51

Tony Vincent

13,354
7
49
68

Select the first row for each group in MySQL?

3 Answers3