1

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?

Improve this question
1
  • I think your query is clear enough apart from the in statement. I think the select MAX(Year) return a single value so you need an = sign instead of in.
    – Ertunç
    Commented Oct 16, 2012 at 5:59

2 Answers 2

Reset to default
2

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
Improve this answer
2
  • Yes, I'm more SQL Server person, I've edited post with suggestion for MySQL, I can not try it - have no MySQL access
    – roman
    Commented Oct 16, 2012 at 6:00
  • Except for identifier quoting, which in MySQL should use backticks rather than double-quotes (which indicate a string literal), this looks fine.
    – eggyal
    Commented Oct 16, 2012 at 7:35
1

try this:

select t1.months from
(select top 1 t.months as months,max(t.years) as years from
(select years,max(months) as months from cal group by years) t
group by t.months) t1
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.