SQL ORDER BY CASE

Introduction to SQL ORDER BY CASE

SQL order by case can be used when we have to order the data on a conditional basis and define the criteria on which the ordering will be done based on a certain condition. The order by clause can be helpful for sorting data in ascending or descending manner by simply specifying the column on which the sorting is to be done or multiple columns if sorting is to be done considering all of them one by one. But in case if the sorting criteria that are the column on which the sorting is to be done need to be specified in a dynamic manner and the column considered for sorting will be decided during the execution of query statement. For this, the case statement can be used inside the order by clause.

In this article, we will study the syntax of defining the order by clause dynamically using the case statement, study its working, and implement some of the examples on order by with case.

Syntax:

SELECT
column1, column2,..., columnm
FROM
target_table
WHERE
conditions_or_constraints
ORDER BY CASE WHEN condition1 THEN columni ELSE [CASE WHEN condition2 THEN columni ELSE...] END;

The syntax of the ORDER BY clause along with case statement is as shown above. It is the optional clause used in the select clause whenever we need to retrieve the resultset containing multiple column values. It should always be placed after the FROM and WHERE clause in the SELECT clause. Some of the terms used in the above syntax are explained below –

• column1, column2,…, columnm – These are the names of the columns of the target_table table that need to retrieved and fetched in the resultset.
• target_table – Name of the table from where the result is to be fetched.
• conditions_or_constraints – If you want to apply certain conditions on certain columns they can be mentioned in the optional WHERE clause.
• CASE WHEN condition1 THEN columni ELSE [CASE WHEN condition2 THEN columni ELSE…] – case statement can be used in order by clause on an optional basis to provide the facility to order on the column dynamically. Multiple nested case statements can also be used. In them, if the condition evaluates to true then the column name specified after then is taken as criteria to sort on else if the condition evaluates to false then the column name or the case statement mentioned after else is considered for criteria calculation to get the column on which sorting is to be done.

Examples of SQL ORDER BY CASE

Let us consider one table names developers and check its contents using the following query. After retrieving the records of developers table using the below SELECT query –

SELECT * FROM developers;

gives following output –

Now, suppose that in the above contents of the developers’ table, we want to get the resultset containing all the columns of the table developers and that are ordered on the basis of the name of the developer. Also, we have to make sure that the administrator’s name is to retrieved first and then all the ordering on name clause should come into effect.

For this, we can make the use of a case statement inside the order by a clause that specifies that if the position of the developer is Administrator then keep it with 0 order by that is least value which makes it to arrive in the first record else we will assign 1 that is a greater value than 0 to other records that are other than admin positions as order by criteria.

Further, as this is done the administrator’s record will be retrieved first and then the remaining records. Now, the second criterion in the same order by clause will be name column as we have to retrieve all the records that are sorted in alphabetical order of their names. Hence, our query statement will be as shown below with the specified case statement in order by and after ordering of administrator and other developers the remaining developers will be ordered on the basis of their name.

SELECT
*
FROM
`developers`
ORDER BY CASE WHEN POSITION="Administrator" THEN 0
ELSE 1 END, NAME;

The output of the execution of the above query statement will be as follows consisting administrator’s record at the first position and then the remaining developers ordered on basis of their name –

We can make the use of the CASE statement in order by clause usually when we have to segregate certain records and define their order before or after the regular sort on the column. Most of these cases arise in case of handling NULL values, blank values, and displaying those records at the end of the non-null or non-blank records.

Now, we will consider the table named educba_writers whose contents and structure is as shown using the below select query statements output –

SELECT
*
FROM
`educba_writers`;

The output of the execution of the above query statement will be as follows –

Now, we have to order the resultset on the basis of the contents of the column joining date and time. If we do it using the simple order by clause as mentioned in below query the result set will be as follows –

SELECT
*
FROM
`educba_writers`
ORDER BY `joining_date_time` ;

The output of the execution of the above query statement will be as follows –

But, if we have to order the result set such that all the null values will be at the end and the ordered joining date and time values in beginning then we can make the use of case statement in out select query as shown below –

SELECT
*
FROM
`educba_writers`
ORDER BY
CASE
WHEN `joining_date_time` IS NULL
THEN 1
ELSE 0
END,
joining_date_time ;

The output of the execution of the above query statement will be as follows –

Conclusion

We can make the use of the CASE statement inside the ORDER BY statement to define the order of certain columns in the certain expected way as per requirement and then ordering the remaining records as usual on certain other parameters or keeping them as it is. This dynamic behaviour of defining the order of certain records satisfying certain conditions can only be done by using case statement inside the order by clause of the select query statement and mentioning the conditions in the case statements accordingly.

This is a guide to the SQL ORDER BY CASE. Here we discuss the Introduction, syntax, and examples with code implementation respectively. You may also have a look at the following articles to learn more –

1. SQL Temporary Table
2. SQL Select Top
3. SQL Cluster
4. SQL Table Partitioning