Open In App

Using CASE in ORDER BY Clause to Sort Records By Lowest Value of 2 Columns in SQL

Last Updated : 15 Oct, 2021
Improve
Improve
Like Article
Like
Save
Share
Report

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.

Similar Reads

How to Custom Sort in SQL ORDER BY Clause?
By default SQL ORDER BY sort, the column in ascending order but when the descending order is needed ORDER BY DESC can be used. In case when we need a custom sort then we need to use a CASE statement where we have to mention the priorities to get the column sorted. In this article let us see how we can custom sort in a table using order by using MSS
2 min read
How to find all Employee records containing a Specific Word regardless case-sensitive in SQL
In this article, we will cover how to find all Employee records containing a Specific Word regardless of case-sensitive in SQL and will discuss steps by steps. Introduction :We will discuss, how to find all employee records containing a specific word regardless of the case-sensitive. We create the database employee and check for the following query
2 min read
Difference between Having clause and Group by clause
1. Having Clause : Having Clause is basically like the aggregate function with the GROUP BY clause. The HAVING clause is used instead of WHERE with aggregate functions. While the GROUP BY Clause groups rows that have the same values into summary rows. The having clause is used with the where clause in order to find rows with certain conditions. The
3 min read
Difference between order by and group by clause in SQL
ORDER BY clause is used to sort the result set either in ascending or descending order and GROUP BY the statement is used to group the rows that have the same value and it is used with aggregate functions like SUM(), AVG(), MAX(), etc. In this article, we are going to discuss both of these clauses in detail. Let's start with Order by first. Order B
3 min read
Python MariaDB - Order By Clause using PyMySQL
A MySQL client library is employed when we have to use MySQL with other programming languages. The work of PyMySQL is to provide access to MySQL Driver to the required language. Thus, it generates a connection between the programming language and the MySQL Server. OrderBy Clause The OrderBy is used to arrange the result set in either ascending or d
2 min read
SQL | USING Clause
If several columns have the same names but the datatypes do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an EQUIJOIN. USING Clause is used to match only one column when more than one column matches. NATURAL JOIN and USING Clause are mutually exclusive. It should not have a q
2 min read
Combining aggregate and non-aggregate values in SQL using Joins and Over clause
Prerequisite - Aggregate functions in SQL, Joins in SQLAggregate functions perform a calculation on a set of values and return a single value. Now, consider an employee table EMP and a department table DEPT with following structure:Table - EMPLOYEE TABLE NameNullTypeEMPNONOT NULLNUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESA
2 min read
SQL Full Outer Join Using Left and Right Outer Join and Union Clause
An SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss FULL OUTER JOIN using LEFT OUTER Join, RIGHT OUTER JOIN, and UNION clause. Consider the two tables below: Sample Input Table 1: Pu
3 min read
SQL query using COUNT and HAVING clause
Consider a table STUDENT having the following schema: STUDENT (Student_id, Student_Name, Address, Marks) Student_id is the primary column of STUDENT table. Let first create the table structure with CREATE Command in SQL: CREATE TABLE STUDENT (STUDENT_ID NUMBER (4), STUDENT_NAME VARCHAR2 (20), ADDRESS VARCHAR2 (20), MARKS NUMBER (3), PRIMARY KEY (ST
4 min read
SQL Full Outer Join Using Union Clause
In this article, we will discuss the overview of SQL, and our main focus will be on how to perform Full Outer Join Using Union Clause in SQL. Let's discuss it one by one. Overview :To manage a relational database, SQL is a Structured Query Language to perform operations like creating, maintaining database tables, retrieving information from the dat
3 min read
Article Tags :