SQL ORDER BY

The SQL ORDER BY clause has already been briefly described in an earlier article. This article expands on that with more examples and describes common errors where it is used incorrectly.




The syntax is given in Books Online as:

ORDER BY { order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] } [ ,...n ] This means a comma separated list of expressions, each optionally qualified with COLLATE, ASC or DESC.

COLLATE allows a collation other than the one defined for the column to be used in the sort. This allows a case-sensitive ordering, or one for a different language to be used.

ASC and DESC specify whether to sort in ascending or descending order. The default is ASC.

order_by_expression can be a column name, an alias for a column, a positional identifier, or an expression based on one or more columns. Let's cover each of these in turn.

SQL ORDER BY Examples

The following three SELECTs produce identical results. First specifying the column name:

SELECT type_desc AS Description FROM sys.objects ORDER BY type_desc Now specifying an alias:

SELECT type_desc AS Description FROM sys.objects ORDER BY Description And finally a positional identifier:

SELECT type_desc AS Description FROM sys.objects ORDER BY 1 The same applies to aggregate functions. This:

SELECT type_desc AS Description, count(*) AS NumOccurrences FROM sys.objects GROUP BY type_desc ORDER BY count(*) DESC is the same as this:

SELECT type_desc AS Description, count(*) AS NumOccurrences FROM sys.objects GROUP BY type_desc ORDER BY NumOccurrences DESC The order_by_expression does not even have to be included in the SELECT list (with some exceptions, see below). The following lists employees in descending order of age, but doesn't include the age in the results:

SELECT FirstName, LastName FROM Employee ORDER BY Age DESC This extends to the use of complicated expressions or functions. This (very contrived) example sorts employees so that the middle aged are returned first, followed by the young and finally the old:

SELECT FirstName, LastName FROM Employee ORDER BY CASE WHEN Age BETWEEN 30 AND 50 THEN 1 WHEN Age < 30 THEN 2 ELSE 3 END Nested sorts can be performed by specifying more than 1 column. To return employees sorted by department, and by last name within each department you would write this:

SELECT Department, FirstName, LastName FROM Employee ORDER BY Department, LastName

Common Errors

I mention above that the order_by_expression does not have to be included in the SELECT list. There are a couple of exceptions to this. If used with SELECT DISTINCT you will get an error.

SELECT DISTINCT LastName FROM Employee ORDER BY Department This results in the following error:

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

The same is true of a UNION.

SELECT LastName FROM Employee UNION SELECT LastName FROM Customer ORDER BY Age This results in the following error:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

While on the subject of a UNION statement, you can only sort on the final output (so just one ORDER BY at the end). If you attempt to sort on individual SELECTs within the UNION you get the following error:

Incorrect syntax near the keyword 'UNION'.

The SQL ORDER BY clause is also invalid in subqueries, unless the number of rows is limited via the TOP keyword. The following generated an error:

SELECT * FROM Employee WHERE SocialSecurityNumber = ( SELECT SocialSecurityNumber FROM Customer ORDER BY SocialSecurityNumber) The error message is:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

In addition to subqueries, this error message also refers to derived tables, views, inline functions, etc. I'll ignore these for now as they haven't been covered yet in this series of tutorials.

The solution to this problem is given in the error message; use the TOP keyword. The above SQL can be made to work as follows:

SELECT * FROM Employee WHERE SocialSecurityNumber = ( SELECT TOP 1 SocialSecurityNumber FROM Customer ORDER BY SocialSecurityNumber)

Summary

In this article I have described the basic syntax of the SQL ORDER BY clause, and covered a few common errors where it is not used correctly.

In the next article I describe the SQL LIKE operator, with examples of each type of pattern.

SQL FAQ |  Recent Articles |  SQL Server Administration |  SQL Server Performance
SQL Server Scripts |  SQL Server Error Reference |  Learn SQL |  Reviews |  Miscellany
My Services |  Contact Me |  Privacy Policy |  Site Map

© Richard Fryar. All Rights Reserved.