SQLORDER BY

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

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

Sorting by multiple columns

SELECT DisplayName, JoinDate, Reputation
FROM Users
ORDER BY JoinDate, Reputation
DisplayNameJoinDateReputation
Community2008-09-151
Jeff Atwood2008-09-1625784
Joel Spolsky2008-09-1637628
Jarrod Dixon2008-10-0311739
Geoff Dalgas2008-10-0312567

Sorting by column number (instead of name)

You can use a column's number (where the leftmost column is '1') to indicate which column to base the sort on, instead of describing the column by its name.

Pro: If you think it's likely you might change column names later, doing so won't break this code.

Con: This will generally reduce readability of the query (It's instantly clear what 'ORDER BY Reputation' means, while 'ORDER BY 14' requires some counting, probably with a finger on the screen.)

This query sorts result by the info in relative column position 3 from select statement instead of column name Reputation.

SELECT DisplayName, JoinDate, Reputation
FROM Users
ORDER BY 3
DisplayNameJoinDateReputation
Community2008-09-151
Jarrod Dixon2008-10-0311739
Geoff Dalgas2008-10-0312567
Joel Spolsky2008-09-1625784
Jeff Atwood2008-09-1637628

Order by Alias

Due to logical query processing order, alias can be used in order by.

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY jd, rep

And can use relative order of the columns in the select statement .Consider the same example as above and instead of using alias use the relative order like for display name it is 1 , for Jd it is 2 and so on

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY 2, 3

Customizeed sorting order

To sort this table Employee by department, you would use ORDER BY Department. However, if you want a different sort order that is not alphabetical, you have to map the Department values into different values that sort correctly; this can be done with a CASE expression:

NameDepartment
HasanIT
YusufHR
HillaryHR
JoeIT
MerryHR
KenAccountant
SELECT *
FROM Employee
ORDER BY CASE Department
         WHEN 'HR'         THEN 1
         WHEN 'Accountant' THEN 2
         ELSE                   3
         END;
NameDepartment
YusufHR
HillaryHR
MerryHR
KenAccountant
HasanIT
JoeIT