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.

Answer 2

In aggregations (--> GROUP BY), you can only select/use fields in combination with aggregation functions (e.g. SUM, MAX, MIN) and fields listed in the GROUP BY-clause.

Simple sample:

A    |   B
-----+-----
1    |   2
1    |   3

if you'd write SELECT A,B FROM table GROUP BY A, that would yield:

A    |   B
-----+-----
1    |{2,3}

but that is not possible (B has 2 values in one row!?!). You have to do something with the values of B which groups them together, too. So two possibilities:

1: Add B in the GROUP BY-clause

SELECT A,B FROM table GROUP BY A,B

yields

A    |   B
-----+-----
1    |   2
1    |   3

2: Use an aggregation function on B

SELECT A,MAX(B) FROM TABLE GROUP BY A,B

gives you

A    |   B
-----+-----
1    |   3

The same arguments apply to the ORDER BY clause.

Most of the times when you want to write a statement like the first one I showed up with, possibility 1 is the solution, since you may know that A and B belong together (common sample: UserId and UserName) but the RDBMS does not know it!