在MySQL中为GROUP获取MAX行 [英] Get MAX row for GROUP in MySQL

问题描述

我有以下资料：

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 |
+---------+----------+----------+--------+

我想得到一个MySQL查询，该查询获取行的 data 每个 someId 的最高数字。请注意， id 是唯一的，但 number 不是

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

推荐答案

对Naeel的答案稍作修改，但对于任何someId只返回单个数据结果，即使存在领带也应该添加 GROUP BY ：

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小提琴此处

这篇关于在MySQL中为GROUP获取MAX行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！