MySQL 5.7 - how to get id for a max value and group by query?

Question

I work on MySQL 5.7, I want to get the ID and TITLE of the last article for each category. For example I have the following table 'Article':

| ID | TITLE   | DATE       | FK_CATEGORY |
| 1  | title 1 | 2016-10-05 |      1      |
| 2  | title 2 | 2017-10-02 |      1      |
| 3  | title 3 | 2015-10-10 |      2      |
| 4  | title 4 | 2017-04-20 |      2      |
| 5  | title 5 | 2017-04-12 |      3      |

For this example I expect the result to be:

| 2  | title 2 | 2017-10-02 |      1      |
| 4  | title 4 | 2017-04-20 |      2      |
| 5  | title 5 | 2017-04-12 |      3      |

I have tryed this request but it does not work :

SELECT id, title, MAX(date), fk_category
FROM article
GROUP BY fk_category
ORDER BY date DESC

What query do I have to use ?

PS: I do not want to disable ONLY_FULL_GROUP_BY SQL mode

Answer 1

Even if you were to disable MySQL's ONLY_FULL_GROUP_BY mode, and your current query were to run, it might not yield the correct results. Also, you would not be able to port the query to most other databases. A better approach is to use a subquery which finds the most recent date for each category. Then, join article to this subquery to get the result set you want.

    SELECT t1.*
    FROM article t1
    INNER JOIN
    (
        SELECT FK_CATEGORY, MAX(DATE) AS MAX_DATE
        FROM article
        GROUP BY FK_CATEGORY
    ) t2
        ON t1.FK_CATEGORY = t2.FK_CATEGORY AND
           t1.DATE        = t2.MAX_DATE
    ORDER BY date DESC

Answer 2

The only way is to join the table back, something like:

select a.id, a.date, a.title, a.fk_category
from article a
join
(
SELECT id, MAX(date) as date, fk_category
FROM article
GROUP BY fk_category
) x using (id, date, fk_category)
ORDER BY a.date DESC;