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...
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):
Method 2 (using window function , data will show in ascending order, 2013,2014,...,2023):