Custom ORDER BY For SQL Server

Semester Based Custom ORDER BY For SQL Server

Working with lots of academic data I often have the need to sort based on the semester.  Here is a quick sample of how to build a custom order by list that will give you ordered results based on the semester (in descending order)

ORDER BY
    ACADEMIC_YEAR DESC,
    CASE ACADEMIC_TERM
        WHEN 'WINTER' THEN 1
        WHEN 'FALL' THEN 2
        WHEN 'SUMMER' THEN 3
        WHEN 'SPRING' THEN 4
    END

-------------------------------------------------------------------------------------------

Defining a Custom ORDER BY Sort Order
The SQL ORDER BY clause is very useful to sort your results quickly and easily. An underlying problem with ORDER BY is that, in its simplest implementation, we are quite limited to how it actually functions. We leave the SQL Engine of our database of choice to decide what the order is. Typically we could use the ORDER BY clause to either modify the sort order -




SELECT * FROM EMPLOYEES ORDER BY EMP_ID [ASC|DESC]

or even numerically order the columns to sort based on the SELECT clause -




SELECT EMP_ID, NAME FROM EMPLOYEES ORDER BY 1,2

These approaches give us some control over how this clause functions, however it isn't very friendly or functional when we have very specific needs. What if we need to specify a custom order that the basic functions of ORDER BY are incapable of?

Using our Employee example again, what if we need to order by a specific seniority level - say we want the supervisors to appear first, then associates, then executives. We may get lucky with a standard ORDER BY, but odds are it won't work out as we intended. For simplicity's sake, the seniority level is a field within the EMPLOYEE table.

To solve this issue, we can utilize a Select (or Switch) case familiar to programmers within the ORDER BY clause. The syntax would look similiar to the following:




...

ORDER BY CASE [Column Name] WHEN [Value1] THEN [Position 1] WHEN [Value2] THEN [Position 2] END

This structure allows us to layout the custom order scheme needed for our solution. Applying this to our sample problem, we end up with a query such as this:




SELECT EMP_ID, NAME, SENIORITY
FROM EMPLOYEES
ORDER BY CASE SENIORITY
WHEN 'SUPERVISOR' THEN 1
WHEN 'ASSOCIATE' THEN 2
WHEN 'EXECUTIVE' THEN 3
END

The result query would then be ordered precisely as required.

Posted in: SQL Server