In this article, we will see how to use CASE in the ORDER BY clause to sort records by the lowest value of 2 columns in SQL.

CASE statement:

This statement contains one or various conditions with their corresponding result. When a condition is met, it stops reading and the corresponding result gets returned (similar to the IF-ELSE statement).

It returns the value specified in the ELSE clause within the CASE statement if no conditions are true. It will return NULL if the ELSE clause is not present in the statement.

CASE Syntax:

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   WHEN condition3 THEN result3
   ELSE result
END;

ORDER BY: This keyword is used to sort the result-set in ascending or descending order. It sorts the records in ascending order by default. ASC or DESC is the keyword to sort the record in ascending or descending order respectively.

ORDER BY Syntax:

SELECT column_name1, column_name2, ...
FROM table_name
ORDER BY column_name1, column_name2, ... ASC|DESC;

Step 1: Creating the Database

Use the below SQL statement to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks.

Query:

USE geeks;

Step 3: Table definition

We have the following demo_table in our geek’s database.

Query:

CREATE TABLE demo_table(
ID int,
NAME VARCHAR(20),
APPLE int,
ORANGE int);

Step 4: Insert data into the table

Query:

INSERT INTO demo_table VALUES
(1, 'Romy', 45, 7),
(2, 'Rinkle', 20, 25),
(3,'Shalini', 22, 29),
(4, 'Akanksha',50, 55),
(5,'Ayushi', 40, 13);

Step 5: Use of CASE in ORDER BY clause to sort record by the lowest value of 2 column 

For demonstration, we will order the table using the lowest value of the ‘ORANGE’ and ‘APPLE’ columns.

Query:

SELECT * FROM demo_table
ORDER BY CASE  
          WHEN  APPLE< ORANGE THEN APPLE
          ELSE ORANGE
        END

Output:

Output Explanation:

• ID= 1 is first because the Orange column of ID=1 has the lowest record in the table. 
• ID=5 is second because the Orange column of ID=5 has the second-lowest record in the table.
• ID= 2 is third because the Apple column if ID=2 has the third-lowest record in the table and so on.