SQL Use ORDER BY with TOP to return the top x rows based on a column's value


Example

In this example, we can use GROUP BY not only determined the sort of the rows returned, but also what rows are returned, since we're using TOP to limit the result set.

Let's say we want to return the top 5 highest reputation users from an unnamed popular Q&A site.

Without ORDER BY

This query returns the Top 5 rows ordered by the default, which in this case is "Id", the first column in the table (even though it's not a column shown in the results).

SELECT TOP 5 DisplayName, Reputation
FROM Users

returns...

DisplayNameReputation
Community1
Geoff Dalgas12567
Jarrod Dixon11739
Jeff Atwood37628
Joel Spolsky25784

With ORDER BY

SELECT TOP 5 DisplayName, Reputation
FROM Users
ORDER BY Reputation desc

returns...

DisplayNameReputation
JonSkeet865023
Darin Dimitrov661741
BalusC650237
Hans Passant625870
Marc Gravell601636

Remarks

Some versions of SQL (such as MySQL) use a LIMIT clause at the end of a SELECT, instead of TOP at the beginning, for example:

SELECT DisplayName, Reputation
FROM Users
ORDER BY Reputation DESC
LIMIT 5