SQL Order By with case when

user_10535439 0
Viewed 328

I am trying to understand the ORDER BY with CASE WHEN.

My aim is to understand it fundamentally for that I had different use cases created

My base table is as below

|   Name |
|--------|
|    BPM |
|    BXR |
| Others |
|    XZA |
|    XYZ |
|    PQR |
|    ABC |

Query 1: Basic ORDER BY

SELECT * 
FROM City 
ORDER BY Name

Query 1 Result:Gave correct output as below(Name column sorted in ascending order)

|   Name |
|--------|
|    ABC |
|    BPM |
|    BXR |
| Others |
|    PQR |
|    XYZ |
|    XZA |

Query 2: I want Others at last

SELECT * 
FROM City
ORDER BY CASE 
         WHEN Name = 'Others' THEN 1
         ELSE 0
         END

Query 2 Result: I got partially correct result.I got Others at last but other names I expected it to be in ascending order.They actually appear the way they are in base table.

|   Name |
|--------|
|    BPM |
|    BXR |
|    XZA |
|    XYZ |
|    PQR |
|    ABC |
| Others |

I am also not getting what does 0 and 1 actually mean in the ORDER BY statement.

Query 3: I want BXR and Others at last.

 SELECT * 
    FROM City
    ORDER BY CASE
             WHEN Name = 'BXR' THEN 1
             WHEN Name = 'Others' THEN 2
             ELSE 0
             END

Query 3 Result: I got partially correct result.I got 'Others' and 'BXR' at last but other Name are not in alphabetical order.Same as seen in Query 2.Here also I am not understanding the significance of 0, 1,2

|   Name |
|--------|
|    BPM |
|    XZA |
|    XYZ |
|    PQR |
|    ABC |
|    BXR |
| Others |

Query 4: I want Others and PQR at top.

SELECT * 
FROM City
ORDER BY CASE
         WHEN Name = 'PQR' THEN 0
         WHEN Name = 'Others' THEN 1
         ELSE 2
         END

QUery 4 Result: I get PQR and Others at top but the remaining names are not in aplhabetical order.

|   Name |
|--------|
|    PQR |
| Others |
|    BPM |
|    BXR |
|    XZA |
|    XYZ |
|    ABC |

My assumption about 0, 1, 2 is that they are just numbers deciding the "order" in which a record should be. (The record having 0 should be kept first and if all other records have 1 then should be sorted alphabetically) (If there are '0', '1','2', in record with 0 should be first, record with 1 should be second all other record having 2 should be sorted alphabetically)

Correct me if I am wrong with this

SQLFiddle

case-when sql-order-by sql
3 Answers

You need to add name also in order by

DEMO

SELECT * 
FROM City
ORDER BY CASE
         WHEN Name = 'PQR' THEN 0
         WHEN Name = 'Others' THEN 1
         ELSE 2
         END,name

OUTPUT:

**Name**

PQR
Others
ABC
BPM
BXR
XYZ
XZA
user_69148640

We can also ORDER BY using FIELD:

SELECT * 
FROM City
ORDER BY FIELD(Name, 'Others', 'PQR') DESC, name;

enter image description here

Demo

The behavior of FIELD is such that it will return 1 for Others, 2 for PQR, and 0 for any other name. So, we use a descending order to ensure that PQR appears first, followed by Others, followed all other names.

user_18632290

You may keep the name column in the else case as :

SELECT * 
FROM City
ORDER BY CASE
         WHEN Name = 'PQR' THEN 0
         WHEN Name = 'Others' THEN 1
         ELSE Name
         END

since always numbers has precedence over alphabets.

SQL Fiddle Demo

user_58413060
Related