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

niknxzdl  于 2023-02-28  发布在  其他
关注(0)|答案(1)|浏览(187)

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

2013
2014
2015
2016
2017
2018
2019
etc...
xn1cxnb4

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

相关问题