SQL Server How to filter results by last 10 years in int column

niknxzdl 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(99)

I have a table with a year field called Taxyear that are of the integer type. I want to create a query that will always give me the most recent taxyear, plus the past 10. If this was for one and done data request I would just filter by taxyear between 2023 and 2013 but I need this query to always look at the most recent 10 taxyears. There is more to this query but I'm breaking it down as simple as I can to isolate where the problem is that I'm having.

I have tried this query below but this just gives me all the years from 1980 - 2023. Again this isn't a date column even though it's year, it's smallint.

SELECT Taxyear
FROM Property
GROUP BY Taxyear
HAVING  taxyear between Max(taxyear) -10 and Max(taxyear)

The results I'm expecting are

sql-server

来源：https://stackoverflow.com/questions/75465209/how-to-filter-results-by-last-10-years-in-int-column

1条答案

按热度按时间

xn1cxnb41#

The reason why the original query doesn't work is the group by condition itself is taxyear, so max(taxyear) means max of current row tax year, which is always true.

There are many ways to calculate this, I just list 2 methods here:

Method 1(using limit clause, data will show in descending order, 2023,2022,...,2013):

SELECT
   taxyear
FROM
   Property
GROUP BY taxyear
ORDER BY taxyear DESC
LIMIT 11

Method 2 (using window function , data will show in ascending order, 2013,2014,...,2023):

select
    distinct taxyear
from
(SELECT
   taxyear,
   max(taxyear) over() as max_taxyear
from
   Property
) t
where
   max_taxyear - taxyear <=10
order by taxyear

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server How to filter results by last 10 years in int column

1条答案

相关问题

热门标签

最新问答