SQL Server 在表格中为每个月的日期排序

7tofc5zh 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(159)

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?

sql-server

来源：https://stackoverflow.com/questions/74739918/rank-the-dates-in-a-table-for-each-month

2条答案

按热度按时间

3qpi33ja1#

You just add the PARTITION BY clause to ROW_NUMBER() and partition by month (and year since your data might cross a year boundary).

WITH cte AS (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY DATEPART(year, loaddate), DATEPART(month, loaddate) ORDER BY loaddate desc) AS myrank
    FROM #MyTable
)
SELECT *
FROM cte
WHERE myrank <= 3
ORDER BY loaddate;

Note: The CTE is doing the same thing as your sub-query - don't let that confuse you - I just prefer it for neatness.

赞(0）回复(0）举报 2022-12-10

zour9fqk2#

If I understand you request I think this would help you:

SELECT myrank, loaddate , monthofyear
    FROM
    (
      SELECT ROW_NUMBER() OVER (partition by  month(loaddate)  ORDER BY loaddate 
       desc) 
        AS myrank, loaddate , month(loaddate) as monthofyear
       FROM Db15.dbo.mytable
      group by loaddate
     ) as daterank
     WHERE daterank.myrank <= 3

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 在表格中为每个月的日期排序

2条答案

相关问题

热门标签

最新问答