MySQL: Condition Based Sorting

Introduction

Sometimes, we need to sort a result set based on some conditions. Let’s take few real time scenarios and then try to resolve these with the help of CASE WHEN.

Scenario 1

Sometimes, we need an element to be placed at the bottom of the list. For example, a Dropdownlist containing educational qualifications may contain an item “Other”. While showing all the educational qualifications from database, we may need this particular element (i.e, “Other”) to be moved to the bottom of the list. If we try to sort this list using usual ORDER BY keyword, then we may not get the desired sequence. To get the desired result, we need to use CASE WHEN keyword along with the ORDER BY. Let’s illustrate this with an example:

Simple ORDER BY

SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY Qualification

Output

ORDER BY with CASE WHEN

SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY CASE WHEN Qualification='Other' 
THEN 'ZZZZZ' ELSE Qualification END

Output

Explanation

Here, we have set the value of item “Other” as “ZZZZZ” while the sorting is being done. Generally “ZZZZZ” will be the last item in any real world list and hence will move to the last.

Scenario 2

In some other cases, we may need the desired item to be moved to the top of the result set. An example for this situation can be, a Dropdownlist containing items for Book Category. The list may have an item with text “General”, which is meant for those books whose category is not clearly known. Let’s assume that most of the books are supposed to be of this category. Then, we have to place this item in the first index of the Dropdownlist so that it will be easier for user to pick the item. Now, we need a little modification in the ORDER BY clause as follows:

ORDER BY CASE WHEN Qualification='General' THEN NULL ELSE Qualification END

Please share your feedback! Thanks!

You may also like:

• Creating a simple stored procedure in MySQL

CodeProject