### Extended sort key columns

The reason why what you want to do doesn't work is because of the logical order of operations in SQL, which, for your first query, is (simplified):

`FROM MonitoringJob`

`SELECT Category, CreationDate`

i.e. add a so called *extended sort key column*
`ORDER BY CreationDate DESC`

`SELECT Category`

i.e. remove the *extended sort key column* again from the result.

So, thanks to the SQL standard *extended sort key column* feature, it is totally possible to order by something that is not in the `SELECT`

clause, because it is being temporarily added to it behind the scenes.

### So, why doesn't this work with `DISTINCT`

?

If we add the `DISTINCT`

operation, it would be added between `SELECT`

and `ORDER BY`

:

`FROM MonitoringJob`

`SELECT Category, CreationDate`

`DISTINCT`

`ORDER BY CreationDate DESC`

`SELECT Category`

But now, with the *extended sort key column* `CreationDate`

, the semantics of the `DISTINCT`

operation has been changed, so the result will no longer be the same. This is not what we want, so both the SQL standard, and all reasonable databases forbid this usage.

### Workarounds

It can be emulated with standard syntax as follows

```
SELECT Category
FROM (
SELECT Category, MAX(CreationDate) AS CreationDate
FROM MonitoringJob
GROUP BY Category
) t
ORDER BY CreationDate DESC
```

Or, just simply (in this case), as shown also by Prutswonder

```
SELECT Category, MAX(CreationDate) AS CreationDate
FROM MonitoringJob
GROUP BY Category
ORDER BY CreationDate DESC
```

I have blogged about SQL DISTINCT and ORDER BY more in detail here.