0

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

Douglas Gaskell
  • 9,017
  • 9
  • 71
  • 128
  • Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – PM 77-1 Dec 02 '16 at 00:21
  • @PM77-1 I am actually referencing that post for guidance on how to accomplish my goal. However, this post IS NOT about how to select a max value for a column. It's about why the version seems to be mismatched, which is what I explicitly asked. – Douglas Gaskell Dec 02 '16 at 00:26
  • You need to Google `MySQL GROUP BY Extensions` and read it carefully. You will understand that the reason for "*mismatch*" and to fix it you will need to use techniques from the post I referenced. – PM 77-1 Dec 02 '16 at 00:36
  • @PM77-1 there is a lot of information regarding this with that kind of search, if you are thinking of a specific article why not link to it? I feel as if you are being intentionally cryptic about what the issue or my lack of understanding is. – Douglas Gaskell Dec 02 '16 at 00:39
  • [RTFM](https://en.wikipedia.org/wiki/RTFM) – PM 77-1 Dec 02 '16 at 00:45
  • @PM77-1 Why are you intentionally unhelpful? You realize that the post you linked to would also be unnecessary if the OP read and understood the entirety of the MySQL Reference docs? – Douglas Gaskell Dec 02 '16 at 00:50

2 Answers2

3

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
aaronsteers
  • 2,277
  • 2
  • 21
  • 38
  • Thank you for your constructive and helpful explanation. I realize I had a fundamental misunderstanding of how the Group By functioned. I was trying something similar to what you have there, but I was including the 3rd field in the inner query. So I was focusing only on the results of the inner query in my question. – Douglas Gaskell Dec 02 '16 at 00:57
  • I'm glad this was helpful. Thanks! – aaronsteers Dec 02 '16 at 01:16
-1

Edit: So MySQL allows it, but I don't think it's a good practise to use it.

You are trying to query non-aggregated data from an aggregated query. You should not do that.

A GROUP BY takes the field it should make group of rows with (in your case, what you say with your GROUP BY is: give me a result per different group_id) and gives a result (the aggregated data) based on the grouping.

Here, you try to access non aggregated data (rubric_id in your case). For some reason, the query does not crash and picks a "random" id in your aggregated data.

Res260 - Émilio Gonzalez
  • 11
  • 3