Search code examples
mysqlsqlmaxcomposite

How to find max from a composite set of rows?

I have a table such as :

Year | Month
------------
2011    10   
2011    11   
2012    5   
2012    6

The query should return the latest "Month" for the latest "year".

Currently I'm doing something like

Select MAX("Month") from table where "Year" in (select MAX("Year") from table)

But I'm not satisfied with query. Can someone suggest a more compact and cleaner way?

Solution

  • try this

    select top 1
    "Month"
from table
order by "Year" desc, "Month" desc

    all right, for MySQL I think it should be

    select
    "Month"
from table
order by "Year" desc, "Month" desc
limit 1