I need to find the last three distinct loaddates for each month in various tables for reporting purposes. Example: If I have data from 2021 February to today: I need the three loaddates of Feb 2021, March 2021 and so on till. Dec 2022
So far, I'm able to create the below query in SQL Server which gives me the result for a particular month that I pass in the where condition.
SELECT ROW_NUMBER() OVER (ORDER BY loaddate desc) AS myrank, loaddate
FROM <tablename>
where year(loaddate) = 2022 and month(loaddate) = 6
group by loaddate
It gives me:
myrank loaddate
1 2022-08-29 00:00:00.000
2 2022-08-25 00:00:00.000
3 2022-08-18 00:00:00.000
4 2022-08-17 00:00:00.000
5 2022-08-11 00:00:00.000
From this I can easily select the top three dates with the below query:
SELECT myrank, loaddate
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY loaddate desc) AS myrank, loaddate
FROM <tablename>
where year(loaddate) = 2022 and month(loaddate) = 6
group by loaddate
) as daterank
WHERE daterank.myrank <= 3
which outputs:
rank loaddate
1 2022-08-29 00:00:00.000
2 2022-08-25 00:00:00.000
3 2022-08-18 00:00:00.000
But this is only for one month. I'm manually passing the month number in the where condition. How to make this ranking query give me the the last 3 distinct loaddates for each month of data that exists in the table?
And also, how to do I run such a generic query on list of 400+ tables instead of changing the tablename manually for each table in the list?
2条答案
按热度按时间3qpi33ja1#
You just add the
PARTITION BY
clause toROW_NUMBER()
and partition by month (and year since your data might cross a year boundary).Note: The CTE is doing the same thing as your sub-query - don't let that confuse you - I just prefer it for neatness.
zour9fqk2#
If I understand you request I think this would help you: