Name

ORDER BY Clause

The ORDER BY clause specifies the sort order of the result set retrieved by a SELECT statement.

Platform

Command

MySQL

Supported, with limitations

Oracle

Supported, with variations

PostgreSQL

Supported, with variations

SQL Server

Supported, with limitations

SQL2003 Syntax

ORDER BY {sort_expression [COLLATE collation_name]
   [ASC | DESC]}[, ...]

Keywords

ORDER BY

Specifies the order in which rows should be returned by a query. You should not anticipate a specific ordering if you exclude the ORDER BY clause, even if you specify a GROUP BY clause and it appears that a sort has been done.

sort_expression

Specifies an item in the query that will help determine the order of the result set. You can have multiple sort_expressions. They are usually column names or column aliases from the query; however, they may also be expressions like (salary * 1.02). SQL92 allowed the use of ordinal positions for sort_expressions, but this functionality has been deprecated and should not be used in SQL2003 queries.

COLLATE collation_name

Overrides the default collation of the sort_expression and applies the collation_name to the sort expression for the purposes of the ORDER BY clause.

ASC | DESC

Specifies that the sort_expression should be returned in either ascending order (ASC) or descending order (DESC).

Rules at a Glance

The ORDER BY clause should reference columns as they appear in the select item list of the SELECT statement, preferably using their aliases (if aliases exist). For example:

SELECT au_fname ...

Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.