view raw
Sammy Sammy - 11 months ago 40
SQL Question

SQL max n versions for each week of each year

I have a data table with [YEAR], [WEEKNO], [VERSIONNO] cols (and others).

I want to output the whole row (each column) of the latest n number of VERSIONNO's for each WEEKNO in each YEAR.

What's the best way to do it in SQL?


You would use row_number():

select t.*
from (select t.*,
             row_number() over (partition by year, weekno order by versionno desc) as seqnum
      from t
     ) t
where seqnum <= n    -- Your value goes here

row_number() is ANSI-standard functionality supported by most databases.