CODEWITHSUNDEEP
mysqlsql
patrick
patrick

Reputation: 11711

getting rows with the highest value grouped by field from a MySQL database

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:

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

Upvotes: 2

Views: 240

Answers (1)

John Woo
John Woo

Reputation: 263693

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.

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

Upvotes: 3

Related Questions