Find out max available year and then show all records between two years-CodePudding

I'm trying to select from a table the max available year and based on the max year to show results for 10 years back.

Ideally, something like this

 ---- ------------ ------------ 
| id | year       |    data    |
 ---- ------------ ------------ 
|  1 | 2008       |   12453    |
|  2 | 2019       |   12453    |
|  3 | 2005       |   12453    |
|  4 | 2000       |   12453    |
|  5 | 2018       |   12453    |
|  6 | 2020       |   12453    |
|  7 | 2021       |   12453    |
 ---- ------------ ------------

Find out the max year - in this case, 2021
Select everything between 2021 and 2011(including both - start and end years)

In this case, the result should be

 ---- ------------ ------------ 
| id | year       |    data    |
 ---- ------------ ------------ 
|  2 | 2019       |   12453    |
|  5 | 2018       |   12453    |
|  6 | 2020       |   12453    |
|  7 | 2021       |   12453    |
 ---- ------------ ------------

What I have so far is this

SELECT MAX(year) as last_year, MIN(year) as first_year 
FROM data_table t1
INNER JOIN administrator t2 ON t1.id=t2.id 
WHERE dates IS NOT NULL 
between t1.last_year - 10
GROUP BY t2.id

The error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'between t1.last_year - 10

CodePudding user response：

From the data sample you gave, this

select * from table_name
where year >= (select max(year) from table_name) - 10

should return the expected output.

Fiddle