CODEWITHSUNDEEP
mysql
furtadofied
furtadofied

Reputation: 13

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

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!

Upvotes: 1

Views: 96

Answers (3)

pcsutar
pcsutar

Reputation: 1831

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

Upvotes: 0

GhoRdoU MaeMoN
GhoRdoU MaeMoN

Reputation: 63

here's a MySQL query for that:

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

Upvotes: 0

Aksen P
Aksen P

Reputation: 4599

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`

Upvotes: 1

Related Questions