Many developers use the CASE statement in the select list of a SQL statement. But not everybody knows that it can be used to do so much more. One example is to use it for advanced sorting.

For example, say that you want to create a guest list for a customer party your company is having. You want the list ordered alphabetically by the names of the people coming. The problem is that some of the contacts are owners, or board members or something, and they need to be at the top of the list (but still sorted alphabetically). The following SQL statement would achieve this:

 SELECT ContactName, ContactTitle, CompanyName FROM CustomersORDER BY CASE ContactTitle           WHEN 'Owner' THEN 1           ELSE 2         END, ContactName ASC