Using CASE in ORDER BY Clause to Sort Records By Lowest Value of 2 Columns in SQL
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.
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 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.
CREATE DATABASE geeks;
Step 2: Using the Database
Use the below SQL statement to switch the database context to geeks.
Step 3: Table definition
We have the following demo_table in our geek’s database.
CREATE TABLE demo_table( ID int, NAME VARCHAR(20), APPLE int, ORANGE int);
Step 4: Insert data into the table
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.
SELECT * FROM demo_table ORDER BY CASE WHEN APPLE< ORANGE THEN APPLE ELSE ORANGE END
- 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.