CODEWITHSUNDEEP
sqlcase-when
CodeHunter
CodeHunter

Reputation: 2082

Understanding of CASE with ORDER BY in SQL

I am trying to revise my SQL concepts, and while doing so, I faced a curious case which I was not able to understand. Below is my schema and data:

CREATE TABLE Customer 
(
    name varchar(255),
    city varchar(255),
    country varchar(255),
    id int
);

INSERT INTO customer VALUES ("ram923", null, "aIndia3",1);
INSERT INTO customer VALUES ("ram92", null, "dIndia3",1);
INSERT INTO customer VALUES ("ram83", null, "dIndia4",1);
INSERT INTO customer VALUES ("ram94", null, "dIndia4",1);
INSERT INTO customer VALUES ("ram", "city1", "bIndia1",1);
INSERT INTO customer VALUES ("ram1", "city2", "aIndia1",1);
INSERT INTO customer VALUES ("ram2", "city3", "aIndia1",1);
INSERT INTO customer VALUES ("ram3", "city4", "bIndia2",1);
INSERT INTO customer VALUES ("ram4", "city5", "bIndia2",1);
INSERT INTO customer VALUES ("ram8", null, "bIndia2",1);
INSERT INTO customer VALUES ("ram9", null, "bIndia2",1);
INSERT INTO customer VALUES ("ram5", "city6", "cIndia3",1);
INSERT INTO customer VALUES ("ram6", "city7", "dIndia4",1);
INSERT INTO customer VALUES ("ram67", "city71", "dIndia3",1);
INSERT INTO customer VALUES ("ram622", null, "eIndia3",1);
INSERT INTO customer VALUES ("ram81", null, "cIndia3",1);

Then I execute this select query which would use CASE statement with ORDER BY to order the customers by City. However, if City is NULL, then order by Country:

SELECT 
    name, City, Country
FROM
    Customer
ORDER BY
    (CASE
        WHEN City IS NULL THEN Country
        ELSE City
     END);

The output I get is not quite the way I thought it would be:

name    City    Country
------------------------
ram923  (null)  aIndia3
ram8    (null)  bIndia2
ram9    (null)  bIndia2
ram81   (null)  cIndia3
ram     city1   bIndia1
ram1    city2   aIndia1
ram2    city3   aIndia1
ram3    city4   bIndia2
ram4    city5   bIndia2
ram5    city6   cIndia3
ram6    city7   dIndia4
ram67   city71  dIndia3
ram92   (null)  dIndia3
ram83   (null)  dIndia4
ram94   (null)  dIndia4
ram622  (null)  eIndia3

Can anyone explain how the CASE with ORDER BY is working here? Why it would not put all null cities together and then do ORDER BY country?

Upvotes: 0

Views: 203

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35603

The easiest way understand how a case expression affects your order by clause is to output the same case expression as part of the select clause

SELECT
      name , City , Country
    ,(
        CASE 
            WHEN City IS NULL
                THEN Country
            ELSE City
            END
        ) AS sort_by_case
FROM Customer
ORDER BY 
        CASE 
            WHEN City IS NULL
                THEN Country
            ELSE City
            END
;

or:

SELECT
      name , City , Country
    ,(
        CASE 
            WHEN City IS NULL
                THEN Country
            ELSE City
            END
        ) AS sort_by_case
FROM Customer
ORDER BY 
      sort_by_case
;

Upvotes: 0

Akina
Akina

Reputation: 42728

Can anyone explain how the CASE with ORDER BY is working here?

Simply add the expression used in ORDER BY, to output list:

SELECT name, City, Country
, CASE WHEN City IS NULL 
       THEN Country
       ELSE City END expression_from_order_by
FROM customer
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);
name City Country expression_from_order_by
ram923 null aIndia3 aIndia3
ram8 null bIndia2 bIndia2
ram9 null bIndia2 bIndia2
ram81 null cIndia3 cIndia3
ram city1 bIndia1 city1
ram1 city2 aIndia1 city2
ram2 city3 aIndia1 city3
ram3 city4 bIndia2 city4
ram4 city5 bIndia2 city5
ram5 city6 cIndia3 city6
ram6 city7 dIndia4 city7
ram67 city71 dIndia3 city71
ram92 null dIndia3 dIndia3
ram83 null dIndia4 dIndia4
ram94 null dIndia4 dIndia4
ram622 null eIndia3 eIndia3

db<>fiddle here

Now it is absolutely clear how does it works.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

What is hard to understand? You can see the sorting key by replacing the expression, although I would simplify it as:

select c.*, coalesce(city, country)
from customers c
order by coalesce(city, country);

In your sample data, all the country values are before the city values alphabetically.

Upvotes: 1

Related Questions