Get the greatest Year value in mysql after grouping by a column

Question

The below table contains an id and a Year and Groups

     GroupingTable
     id       |    Year    |   Groups    
      1       |     2000   |     A
      2       |     2001   |     B
      3       |     2001   |     A

Now I want select the greatest year even after grouping them by the Groups Column

   SELECT 
         id, 
         Year,
         Groups
   FROM 
        GroupingTable
  GROUP BY 
         `Groups`
  ORDER BY Year DESC

And below is what I am expecting even though the query above doesnt work as expected

     id       |    Year    |   Groups    
      2       |     2001   |     B
      3       |     2001   |     A

Answer 1

You need to learn how to use aggregate functions.

SELECT 
     MAX(Year) AS Year,
     Groups
FROM 
    GroupingTable
GROUP BY 
     `Groups`
ORDER BY Year DESC

When using GROUP BY, only the column(s) you group by are unambiguous, because they have the same value on every row of the group.

Other columns return a value arbitrarily from one of the rows in the group. Actually, this is behavior of MySQL (and SQLite), but because of the ambiguity, it's an illegal query in standard SQL and all other brands of SQL implementations.

For more on this, see my answer to Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Answer 2

Your query misuses the heinously confusing nonstandard extension to GROUP BY that's built in to MySQL. Read this and weep. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

If all you want is the year it's a snap.

  SELECT MAX(Year) Year, Groups
    FROM GroupingTable
   GROUP BY Groups

If you want the id of the row in question, you have to do a bunch of monkey business to retrieve the column id from the above query.

SELECT a.*
  FROM GroupingTable a
  JOIN (
           SELECT MAX(Year) Year, Groups
             FROM GroupingTable
            GROUP BY Groups
       ) b ON a.Groups = b.Groups AND a.Year = b.Year

You have to do this because the GROUP BY query yields a summary result set, and you have to join that back to the detail result set to retrieve the ID.