Get MAX row for GROUP in MySQL

Question

I have the following data:

+---------+----------+----------+--------+
|      id | someId   | number   | data   |
+---------+----------+----------+--------+
|      27 |      123 |        1 | abcde1 |
|      28 |      123 |        3 | abcde2 |
|      29 |      123 |        1 | abcde3 |
|      30 |      123 |        5 | abcde4 |
|      31 |      124 |        4 | abcde1 |
|      32 |      124 |        8 | abcde2 |
|      33 |      124 |        1 | abcde3 |
|      34 |      124 |        2 | abcde4 |
|      35 |      123 |       16 | abcde1 |
|     245 |      123 |        3 | abcde2 |
|     250 |      125 |        0 | abcde3 |
|     251 |      125 |        1 | abcde4 |
|     252 |      125 |        7 | abcde1 |
|     264 |      125 |        0 | abcde2 |
|     294 |      123 |        0 | abcde3 |
|     295 |      126 |        0 | abcde4 |
|     296 |      126 |        0 | abcde1 |
|     376 |      126 |        0 | abcde2 |
+---------+----------+----------+--------+

And I want to get a MySQL query that gets me the data of the row with the highest number for each someId. Note that id is unique, but number isn't

What did you want to happen where you have a tie on the max number for the someId? — ydaetskcoR, Apr 30 '14 at 10:38
in that case, take one of them arbitrarily (or whatever other behavior you can get me ;-) ) — Kalisky, Apr 30 '14 at 10:52
Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — outis, Apr 08 '22 at 03:27

Naeel Maqsudov · Answer 1 · 2014-04-30T12:35:42.163

SELECT someid, highest_number, data
FROM test_1
  INNER JOIN (SELECT someid sid, max(number) highest_number 
              FROM test_1
              GROUP BY someid) t 
  ON (someid=sid and number=highest_number)

Unfortunately it is not look quite efficient. In Oracle it could be possible to user OVER clause without subqueries, but MySQL…

Update 1

If there are several instances of highest number this will returs also several data for each pair of someid and number. To get the only row per each someid we should preaggregate the source table to make someid and number pairs unique (see t1 subquery)

SELECT someid, highest_number, data
FROM 
  (SELECT someid, number, MIN(data) data
   FROM test_1
   GROUP BY
   someid, number) t1
INNER JOIN 
  (SELECT someid sid, max(number) highest_number 
   FROM test_1
   GROUP BY someid) t2
ON (someid=sid and number=highest_number)

Update 2

It is possible to simplify previous solution

SELECT someid,highest_nuimber,
  (select min(data)
   from test_1 
   where someid=t1.someid and number=highest_nuimber)
FROM
   (SELECT someid, max(number) highest_nuimber
    FROM test_1
    GROUP BY someid) t1

If we materialize unique pairs of someid and number than it is possible to use correlated subquery. Unlike a JOIN it would not produce additional rows if highest value of number is repeated several times.

Well, I guess if there's an index on number it should be fine... But how do I get just one per every **someId**? — Kalisky, Apr 30 '14 at 11:38
This should return one for every someId except for when you have a tie. — ydaetskcoR, Apr 30 '14 at 11:42
And once again: the last variant uses corelated subquery instead of JOIN — Naeel Maqsudov, Apr 30 '14 at 12:36

score 1 · Accepted Answer · answered Apr 30 '14 at 11:52

Slight tweak to Naeel's answer but to return just a single data result for any someId even if there's a tie you should add a GROUP BY:

SELECT t1.someid, t1.number, t1.data
FROM Table1 t1
INNER JOIN (SELECT someId sid, max(number) max_number 
            FROM Table1
            GROUP BY someId) t2 
ON (someId = sid AND number = max_number)
GROUP BY t1.someId

SQL Fiddle here

Get MAX row for GROUP in MySQL

2 Answers2

Linked