Search code examples
sql-servert-sql

How to retrieve 10 distinct values of the last records?

For some manual analysis, I had to get the 10 last used codes, with a SQL statement something like this:

SELECT DISTINCT TOP (10) [Status]
FROM [Orders]
ORDER BY [OrderId] DESC

which is not valid SQL and results in an error

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

I was able to work around that with a query like this:

WITH X AS 
(
    SELECT TOP(1000) [Status] 
    FROM [Orders] 
    ORDER BY [OrderId] DESC
)
SELECT DISTINCT * 
FROM X;

And with a bit of trial-and-error, adjusting the number of records, I had my 10 records, problem solved.

But there is still that voice in the back of my head that continues to ask: "But how could I accomplish that with a single query?"

Any ideas?

Solution

  • Why not order by the MAX(OrderId)?

    SELECT TOP (10) [Status]
FROM dbo.[Orders]
GROUP BY [Status]
ORDER BY MAX([OrderId]) DESC;