MySQL: Condition based sorting


AscDescIntrioduction:

Sometimes we need to sort a result set based on some conditions. Let’s take few real time scenarios and than try to resolve these with the help of CASE WHEN. Scenario-1: Sometimes we need an element to be placed at 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: 5         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: 6           EXPLAINATION: 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 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

SQL Server: Custom Sorting


AscDescThis is one of the many custom sorting we do with SQL queries. In few cases we may need to sort all records of a result set in a particular order except one specific row containing some specific value, which we may want to keep on the top of all other rows or we may like to move it to the bottom. Now, let’s relate this to few real time scenarios.

Scenario-1:

Sometimes we need an element to be placed at bottom in the list. For example, a Dropdownlist containing educational qualifications may contain  an item with text as “Other”. While showing all these educational qualifications from database we may need this particular element (i.e, “Other”) to move to the bottom in the list. If we try to sort this list in the SELECT query by normal ORDER BY keyword (like ORDER BY Qualification) then we may not get the desired sequence.

To get the desired result we need to use CASE WHEN keyword along with ORDER BY. Let’s illustrate this with following 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:

5

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:

6

EXPLAINATION:

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 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 belong 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

I’ll love to hear from you.

Thanks

You may also like :

Dynamic ORDER BY in SQL Server

CodeProject

MySQL: Creating a simple stored procedure


Let’s start creating stored procedure with a very simple one and then we will analyse it line by line.

DELIMITER $$

DROP PROCEDURE IF EXISTS MyFirstSP$$
CREATE PROCEDURE MyFirstSP()
BEGIN
 SELECT * FROM MyTable;
END$$

Analysis:

  • By default MySQL treats semicolon(;) as the statement terminator or end of statement. But as we are going to use it inside the procedure body, so we need another different delimiter to state the end of the stored procedure. DELIMITER $$ sets $$ as the statement terminator.
  •  The DROP PROCEDURE IF EXISTS <SPName> statement checks for a duplicate stored procedure with the same name and if there exists any then issue a DROP command. You can skip this line if you are sure that there is no other stored procedure exists with the same name in your selected database.
  • Statement CREATE PROCEDURE marks the start of the stored procedure definition. Here, MyFirstSP is the name of our stored procedure. The stored procedure name followed by a pair of parentheses. The use of these parentheses is to define parameters inside it. In this stored procedure we don’t need any parameters, but we have to put these parentheses as this is mandatory in MySQL unlike SQL Server.
  • The BEGIN Statement marks the start or begining of a block (here the block is the stored procedure itself).In stored procedures, every statements with multiple statements should be enclosed with a block defined by BEGIN and END, where END statement marks the end of the block;
  •  The statement inside the BEGIN .. END is a simple SELECT query, which fetches all the records from MyTable table.

CodeProject