How to get all rows with only the max year within each category of a table in MYSQL (version 5.7)

Question

I have a table of multiple rows with the categories and years:

id	category	year
1	cat1	2022
2	cat1	2022
3	cat1	2021
4	cat2	2019
5	cat2	2019
6	cat2	2018
7	cat2	2018

I am trying to create a query that only gets the rows with the latest year for each category resulting in:

id	category	year
1	cat1	2022
2	cat1	2022
4	cat2	2019
5	cat2	2019

I have tried using GROUP BY but only get one row for each year and category:

SELECT 
 ANY_VALUE(`id`) AS `id`, 
 ANY_VALUE(`category`) AS `category`,
 MAX(`year`) AS `year` 
FROM `my_table` 
GROUP BY `category`,`year` 
ORDER BY `year` DESC, `category`

Any help would be much appreciated!

Answer 1

Reproduce next:

SELECT 
 ANY_VALUE(`my_table`.`id`) AS `id`, 
 ANY_VALUE(`my_table`.`category`) AS `category`,
 ANY_VALUE(`my_table`.`year`) AS `year` 
FROM `my_table`, (SELECT ANY_VALUE(`category`) AS `category`, MAX(`year`) AS `year` FROM `my_table` GROUP BY `category`) AS `my_table2` 
WHERE 
    `my_table`.`year` = `my_table2`.`year` AND
    `my_table`.`category` = `my_table2`.`category`  
ORDER BY `my_table`.`year` DESC, `my_table`.`category` 

Answer 2

here's a MySQL query for that:

SELECT * FROM TAB T1
WHERE YEAR >= ALL (SELECT YEAR FROM TAB T2 WHERE T2.CATEGORY = T1.CATEGORY);

Answer 3

Try below query with INNER JOIN:

SELECT DISTINCT(t1.id), t1.category, t1.year 
FROM my_table t1 
INNER JOIN my_table t2 
ON t1.category = t2.category
WHERE t1.year > t2.year;

This query is more efficient as it doesn't involve any subqueries, aggregate functions or grouping.

Check dbfiddle