How to apply ORDER to GROUP BY

Question

I have the article table, each having a related category (or NULL if not categorized - these articles don't interest me in this case).

I want to get 8 newest articles each from one category in a way that there is always only one article for one category (i.e. never 2 articles from the same category).

This query almost work:

SELECT article.id, article.title, category_container.title  FROM `article`
JOIN `category_container` ON category_container.id = article.category_id
WHERE `category_id` IS NOT NULL GROUP BY `category_id` ORDER BY article.created_at DESC LIMIT 8

The problem is, ORDER doesn't work. I want newest, it returns me the earliest ones (with the smallest id, while it should be the opposite).

So, how to apply ORDER to GROUP BY?

score 1 · Accepted Answer · answered Mar 14 '20 at 16:55

You don't. Your query is broken -- and in the latest versions of MySQL you would properly get an error.

Instead use a filtering method to get the latest rows. Here is a method using a correlated subquery:

SELECT a.id, a.title, cc.title
FROM article a JOIN
     category_container cc
     ON cc.id = a.category_id
WHERE a.created_at = (SELECT MAX(a2.created_at)
                      FROM article a2
                      WHERE a2.category_id = a.category_id
                     )
ORDER BY a.created_at DESC
LIMIT 8;

For performance, you want an index on article(category_id, created_at).

In the more recent versions, this would be even simpler using ROW_NUMBER():

SELECT a.id, a.title, cc.title
FROM (SELECT a.*,
             ROW_NUMBER() OVER (PARTITION BY a.category_id ORDER BY a.created_at DESC) as seqnum
      FROM article a
     ) a JOIN
     category_container cc
     ON cc.id = a.category_id
WHERE seqnum = 1;

Given that your query runs with no errors, you are probably using an older version of MySQL and this won't work.

Thanks. ORDER BY is not necessery in the first query then, I guess; it also assumes that I don't have two articles with the same exact time... maybe id would be better to compare then, I suppose. BTW where does the downvote comes from? — forsberg, Mar 14 '20 at 17:08

How to apply ORDER to GROUP BY

1 Answers1