Douglas Gaskell Douglas Gaskell - 2 days ago 5
MySQL Question

SQL: Group By is mismatching records

I'm trying to get the highest version within a group. My query:

SELECT
rubric_id,
max(version) as version,
group_id
FROM
rubrics
WHERE
client_id = 1
GROUP BY
group_id


The Data:

enter image description here

The Results:

enter image description here

The rubric of ID 2 does not have a version of 2, why is this being mismatched? What do I need to do to correct this?

Edit, not a duplicate:

This is not a duplicate of SQL Select only rows with Max Value on a Column , which is a post I have read and referenced before writing this. My question is not how to find the max, my question is why is the
version
not matched to the correct
ID

Answer

MySQL is confusing you by letting you get away with having a column in your select that isn't in your group by. To resolve the issue, make sure you don't select any field that isn't in the group by.

Instead of trying to get everything in one statement, you will need to use a subquery to find the max_version_id and then join to it.

SELECT T.*
FROM rubrics T
JOIN
(
    SELECT 
        group_id, 
        max(version) as max_version
    FROM    
        rubrics
    GROUP BY
        group_id
) dedupe
 on T.group_id = dedupe.group_id
and T.version_id = dedupe.max_version_id
WHERE
    T.client_id = 1 
Comments