SQL Group with Order by

Question

This feels like it should have a basic solution but I don't seem to be getting it.

Take this query:

            SELECT Category FROM Article
            GROUP BY Category

I want to effectively do this:

            SELECT Category, DatePublished FROM Article 
            GROUP BY Category
            ORDER BY DatePublished DESC

I don't really want to select DatePublished, but it seemed to make sense to order by it. That doesn't work though.

Basically I want to order categories by the most recent DatePublished article.

Answer 1

SELECT  Category
FROM    Article
GROUP BY
        Category
ORDER BY
        MAX(DatePublished) DESC

Since you do a GROUP BY, you need to run some aggregate function over non-grouping columns.

MAX will select the date of last published article from each category and order categories accordingly.