Page 31 - SQL

FROM DEPT
ORDER BY
CASE WHEN REGION IS NULL THEN 1
ELSE 0
END,
REGION

ID REGION CITY DEPARTMENT EMPLOYEES_NUMBER

10 Mid-Atlantic Philadelphia RESEARCH 13

14 Mid-Atlantic New York SALES 12

9 Midwest Chicago SALES 8

12 New England Boston MARKETING 9

5 West Los Angeles RESEARCH 11

15 NULL San Francisco MARKETING 12

4 NULL Chicago INNOVATION 11

2 NULL Detroit HUMAN RESOURCES 9

CASE in ORDER BY clause to sort records by lowest value of 2 columns

Imagine that you need sort records by lowest value of either one of two columns. Some databases
could use a non-aggregated MIN() or LEAST() function for this (... ORDER BY MIN(Date1, Date2)), but
in standard SQL, you have to use a CASE expression.

The CASE expression in the query below looks at the Date1 and Date2 columns, checks which
column has the lower value, and sorts the records depending on this value.

Sample data

Id Date1 Date2

1 2017-01-01 2017-01-31

2 2017-01-31 2017-01-03

3 2017-01-31 2017-01-02

4 2017-01-06 2017-01-31

5 2017-01-31 2017-01-05

6 2017-01-04 2017-01-31

https://riptutorial.com/ 13