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

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

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.

  1. SELECT Taxyear
  2. FROM Property
  3. GROUP BY Taxyear
  4. HAVING taxyear between Max(taxyear) -10 and Max(taxyear)

The results I'm expecting are

  1. 2013
  2. 2014
  3. 2015
  4. 2016
  5. 2017
  6. 2018
  7. 2019
  8. 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):

  1. SELECT
  2. taxyear
  3. FROM
  4. Property
  5. GROUP BY taxyear
  6. ORDER BY taxyear DESC
  7. LIMIT 11

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

  1. select
  2. distinct taxyear
  3. from
  4. (SELECT
  5. taxyear,
  6. max(taxyear) over() as max_taxyear
  7. from
  8. Property
  9. ) t
  10. where
  11. max_taxyear - taxyear <=10
  12. order by taxyear
展开查看全部

相关问题