CODEWITHSUNDEEP
sqlsql-server
Whome
Whome

Reputation: 10400

SQL select max year/month by separate columns

I need to select rows by max YEAR/MONTH value and made this rather complex query. It works but it cannot be the most performance wise solution?

Select Max(hr.HRYear) as HRYear, Max(hr.HRMonth) as HRMonth
From data hr,
  (Select Max(HRYear) as HRYear From data) as atemp
Where hr.HRYear = atemp.HRYear
  and hr.HRMonth = (Select Max(HRMonth) From data Where HRYear=atemp.HRYear)

Example rows and expected result is HRYear=2014, HRMonth=10 values. Key values are integer.

HRYear(int)  HRMonth(int)  Attr1(varchar)
2013         10            aa0
2013         11            aa3
2013         11            bb5
2013         12            aa2
2013         12            aa2
2014          7            cc1
2014          8            dd2
2014         10            ee3
2014         10            ee2

Do you have better idea how to select max year+month combination? I need values to run additional queries.

I have already accepted Gordon's TOP1..ORDERBY solution, but one (now deleted) idea was this query. I have few hundreds test rows so benchmarking is near impossible, but once I hit +millions of rows in a production it remains to be seen what is the most efficient one.

SELECT fst.HRYear, Max(fst.hrmonth) as HRMonth
FROM   data fst
       JOIN (SELECT Max(HRYear) HRYear FROM data) scd
       ON fst.HRYear = scd.HRYear
GROUP  BY fst.HRYear

Upvotes: 3

Views: 7679

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use top or top with ties to get what you want:

select top 1 with ties hr.*
from data hr
order by hr.hryear desc, hr.hrmonth desc;

You would use top 1 hr.* if you wanted the result set to only consist of one row. For instance, if you only wanted the most recent year and month:

select top 1 hr.hryear, hr.hrmonth
from data hr
order by hr.hryear desc, hr.hrmonth desc;

Upvotes: 8

Related Questions