This is the second of two posts in which we talk about sorting records in SQL using ORDER BY. In the first post, we described the basic ORDER BY syntax and the most common ways to use it. In this post, we’ll do a brief recap, then discuss some specialized and advanced uses of ORDER BY, as well as some special issues that may come up with sorting in SQL.
If you want to learn more about SQL, either for your own use, or because you are interested in a career in SQL programming, you might want to take a look at the wide range of SQL courses that are available online — there’s something for everyone, from just-getting-started beginners to advanced students of SQL programming.
ORDER BY: A Quick Recap
In SQL, you sort records using ORDER BY. When you use it with SELECT, for example, it can take a column name as its argument, and it will sort the records accordingly:
SELECT name,city,birthday,favorite_color FROM people ORDER BY city;
Might sort a set of records like this:
Randy Berlin 10/21 yellow Cindy Kuala Lumpur 05/09 orange Bob San Diego 06/07 blue Lin San Francisco 01/17 red Jane Singapore 12/27 green
It can take multiple column names (separated by commas), and you can use column positions (with a number indicating the relative position) or aliases. In many SQL implementations, you can mix names, positions, and aliases. The default sort order is ascending. You can specify descending sort order using DESC, and, if necessary, specify ascending order with ASC; you can independently set the sort order for each column:
ORDER BY city DESC,name ASC,favorite_color DESC, birthday ASC;
Data Type Issues
Note that not all data types can be sorted, and not all data will necessarily sort in the way that you expect. Numbers that are stored as numeric data types will sort numerically, and dates stored as date data types will sort chronologically. But, depending on the SQL implementation, numbers or dates that are stored as text/character data may sort alphabetically. This is an issue which may come up, for example, with part numbers.
In addition, again depending on the SQL implementation, it may not be possible to sort some types of data. In Microsoft’s Transact-SQL, for example, you cannot use ORDER BY to sort the following data types: text (a variable-length field with a maximum length of 2 GB; distinct from the sortable character data types), ntext (the Unicode equivalent of the text data type), image, XML, geometry, and geography.
We mentioned that ORDER BY can take an expression as an argument. This, as you might imagine, opens up a broad range of possibilities, since (among other things) it allows you to base the sort order on a potentially very complex set of conditions. For example (again depending on the SQL implementation and allowable expression syntax), you might use a CASE statement to determine the sort field:
ORDER BY CASE city WHEN 'San Francisco' THEN favorite_color ELSE city END;
This would sort all records for which the city was San Francisco by favorite_color, and all other records by city.
If a column includes null data, you may want to sort the records so that the nulls are at the beginning or the end of the list. In Oracle SQL, you can use NULLS FIRST to set the sort so that nulls are listed first, and NULLS LAST so that they are listed last:
ORDER BY favorite_color NULLS FIRST;
Any records with NULL in the favorite_color column would then be listed first:
Hua Los Angeles 05/14 NULL Raquel Bogota 11/07 NULL Bob San Diego 06/07 blue Jane Singapore 12/27 green Cindy Kuala Lumpur 05/09 orange Lin San Francisco 01/17 red Randy Berlin 10/21 yellow
Note that NULLS LAST is the default if the sort order is ascending, and NULLS FIRST is the default if the sort order is descending. Since the default sort order is ascending, you do not need to specify both ASC and NULLS LAST. This line:
ORDER BY favorite_color;
and this line:
ORDER BY favorite_color ASC NULLS LAST;
In non-Oracle SQL implementations, you may be able to use a CASE statement or a similar conditional expression to set the position of Nulls in the sort.
In Microsoft’s Transact-SQL, you can specify the way that the sort will handle things such as accent, case, and character set by using the COLLATE argument. Collate can be used with the char and varchar data types, and their Unicode equivalents, nchar and nvarchar.
The COLLATE argument takes a collation name, along with a set of options appended to the name, and separated with an underscore. For example, the following command would sort records by name, using Breton collation:
ORDER BY Name COLLATE Breton_100_;
If you wanted to specify that the collation should be case-sensitive and accent-sensitive, you would do so by appending those options:
ORDER BY Name COLLATE Breton_100_CS_AS;
Transact-SQL: OFFSET and FETCH
Transact-SQL also allows you to skip a specified number of records and to return a specified number of records, both after the sort. The OFFSET argument (available starting with SQL Server 2012) returns records starting after the specified number of rows. For example, this command:
ORDER BY Name OFFSET 25 ROWS;
would first sort the records by the Name column, then skip 25 records, and return all records after that.
You can use it to quickly page through a large set of sorted records without sending all of the records to the client application. The user can specify the number of records to skip, for example (based on a ballpark estimate of where the desired records are likely to be), then scroll or page from there to find the record(s) in question.
You can use the FETCH argument with OFFSET to specify the number of records to return after the offset. If you want to skip 150 records, then return the next 35, you could use this command:
ORDER BY Name OFFSET 150 ROWS FETCH NEXT 35 ROWS ONLY;
Both OFFSET and FETCH can take variables or expressions as arguments, so the number of rows to offset and the number of rows to fetch could each be set by conditional expressions, or by variables representing user inputs or calculated values.