How to filter max of a YYYYMM field in SQL?

Question

I have a table where month ID is of YYYYMM format such as 202207 for July 2022. The data type is a number.

I have a scenario where I want to query records from this table only with the most recent month. I'm not able to think of a solution without making the query too complex.

Select a, b
from data
where MonthID = max(MONTHID)

I know the above will not work but something similar perhaps? Any help is much appreciated.

score 0 · Answer 1 · answered Sep 15 '22 at 19:45

0

Order by the MonthID column descending, to get the highest value first. Use fetch first with ties to get all the rows with the highest MonthID value.

Select a, b
from data
order by MonthID desc
fetch first 1 row with ties

answered Sep 15 '22 at 19:45

jarlh

42,561
8
45
63

Thank you, this would work. Would you perhaps know the effect of using fetch first 1 row on the performance of the query? – Prashanth Chitrambalam Sep 15 '22 at 19:49
As long as MonthID is indexed, it should be blazing fast! – jarlh Sep 15 '22 at 19:50
Thank you. Will read up on Fetch First. – Prashanth Chitrambalam Sep 15 '22 at 19:56
FETCH FIRST is great, but OFFSET can be expensive. See https://use-the-index-luke.com/no-offset. – jarlh Sep 15 '22 at 19:59

How to filter max of a YYYYMM field in SQL?

1 Answers1