Problem solve Get help with specific problems with your technologies, process and projects.

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
END DESC

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)

Join the conversation

4 comments

Send me notifications when other members comment.

Please create a username to comment.

You can use CASE expressions for conditional ordering in Oracle too. For more information and examples, check out this article: CASE expressions in Oracle SQL
Cancel
Nice tip. You can learn something new every day..
Cancel
Using CASE has now become a good friend of mine. It's keeping the code a little cleaner and easier to maintain.
Cancel
Just wanted to chime in and add that I do this in DB2, also. The syntax is slightly different but it's easy to Google examples.
Cancel

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close