I have the following table
我有下表
+----+----------+-----+
| id | priority | sub |
+----+----------+-----+
| 1 | 1 | A |
| 2 | 3 | A |
| 3 | 4 | A |
| 4 | 2 | B |
| 5 | 9 | B |
+----+----------+-----+
I'm trying to get the rows with the highest priority for each sub. So this result:
我正在尝试为每个sub获取具有最高优先级的行。所以这个结果:
+----+----------+-----+
| id | priority | sub |
+----+----------+-----+
| 3 | 4 | A |
| 5 | 9 | B |
+----+----------+-----+
I tried grouping, but that gives unpredictable results. My guess was a nested query, first starting to find the highest priority like so:
我尝试过分组,但这会产生不可预测的结果。我的猜测是一个嵌套查询,首先开始找到最高优先级,如下所示:
select max(priority),sub from t group by sub
which gives me
这给了我
+----------+-----+
| priority | sub |
+----------+-----+
| 4 | A |
| 9 | B |
+----------+-----+
But I also need the ID, adding to the query obviously gives me the wrong ID, joining this result with a query on the same table gave me an error (ER_NONUNIQ_TABLE(1066)), which makes sense... Anyone that can push me in the right direction?
但我也需要ID,添加到查询显然给了我错误的ID,加入这个结果与同一个表上的查询给了我一个错误(ER_NONUNIQ_TABLE(1066)),这是有道理的......任何人都可以推我在正确的方向?
1 个解决方案
#1
3
The idea behind the subquery is that it separately gets the maximum value of PRIORITY for each SUB. The result of which will then be joined back to the original table.
子查询背后的想法是它分别获得每个SUB的PRIORITY的最大值。然后,其结果将连接回原始表。
SELECT a.*
FROM tableName a
INNER JOIN
(
SELECT sub, max(priority) maxVal
FROM tableName
GROUP BY sub
) b ON a.sub = b.sub AND
a.priority = b.maxVal
#1
3
The idea behind the subquery is that it separately gets the maximum value of PRIORITY for each SUB. The result of which will then be joined back to the original table.
子查询背后的想法是它分别获得每个SUB的PRIORITY的最大值。然后,其结果将连接回原始表。
SELECT a.*
FROM tableName a
INNER JOIN
(
SELECT sub, max(priority) maxVal
FROM tableName
GROUP BY sub
) b ON a.sub = b.sub AND
a.priority = b.maxVal