Sorting output with CASE expressions for custom sort orders

Quick tip on using CASE statements with Microsoft SQL Server.

Sometimes, a recordset will need to be returned with a non-standard sort order -- something which isn't possible with conventional sort or collation mechanisms. One example would be to have a zero in a numeric column placed at the bottom of the sort order, but to have everything else sorted normally (i.e., top-to-bottom, or bottom-to-top, if desired). Custom sorting like this can be done quickly with the ORDER BY CASE clause:

SELECT datacolumn, numbercolumn FROM table
ORDER BY CASE numbercolumn
        WHEN 0 THEN 0
        ELSE 1

In this particular example, datacolumn can be anything (a nvarchar string, for instance), while numbercolumn is an integer used to do the sorting for the recordset. The way ORDER BY CASE works here is by using WHEN 0 THEN 0 / ELSE 1 to tell SQL Server that everything nonzero in numbercolumn is to be treated as the top of the sort order, while everything with a zero is at the bottom. (The DESC is to further enforce how the sorting works: since everything's being sorted in descending order, the zeroes get put last.)

If you want to specify further criteria for how the sorting works, this can be done by putting a comma after END DESC and specifying further sorting parameters, just as one normally would in an ORDER BY clause.

Note that a CASE statement with an excessive number of parameters will become both unwieldy to program and inefficient to run. A better solution for custom sorting that requires more than two or three criteria might be to create a second table that has all the elements to sort with a sorting-order column. One could then use a JOIN between the two to produce the desired sort order, either for a view or from a stored procedure.

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!

Dig Deeper on SQL-Transact SQL (T-SQL)