SQL ORDER by
Use ORDER BY with TOP to return the top x rows based on a column's value[edit | edit source]
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
With ORDER BY
SELECT TOP 5 DisplayName, Reputation FROM Users ORDER BY Reputation desc
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 column number (instead of name)[edit | edit source]
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
SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY 3
Sorting by multiple columns[edit | edit source]
SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY JoinDate, Reputation
Order by Alias[edit | edit source]
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[edit | edit source]
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:
SELECT * FROM Employee ORDER BY CASE Department WHEN 'HR' THEN 1 WHEN 'Accountant' THEN 2 ELSE 3 END;
This article is an extract of the original Stack Overflow Documentation created by contributors and released under CC BY-SA 3.0. This website is not affiliated with Stack Overflow