How to Filter Null Values to Last While Sorting Ascending in SQL?
Last Updated :
10 Oct, 2021
In this article, we will see how to make null come last when sorting ascending in SQL. SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.
We can make NULL values at last by two methods.
Method 1: Using Minus operator
This method only works with numbers and date values. Minus (-) operator before the column name makes the values sort in reverse order. Minus operator have no effect on NULL values.
Syntax:
SELECT column_name FROM table_name ORDER BY -column_name DESC;
Here, if we add the DESC keyword, all the values get sorted in descending order keeping NULL values at last and minus operator without changing the position of NULL values, sort the non-values in reverse order(ascending order).
Step 1: Creating a 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(
Name VARCHAR(20),
Age int,
City VARCHAR(20));
Step 4: Insert data into the table
INSERT INTO demo_table VALUES('Romy', 22,'Delhi'),
('Rinkle', NULL, 'Delhi'),
('Ayushi', 21, 'Patna'),
('Shalini', 23, 'Patna'),
('Akanksha', NULL, 'Delhi');
Step 5: View the data of the table
Use the below command to see the content of the demo_table:
Query:
SELECT * FROM demo_table;
Output:
Step 6: Sort the Age column making NULL values to appears in last
Query:
SELECT * FROM demo_table ORDER BY -AGE DESC;
Output:
Method 2: Using COALESCE() function
COALESCE(): This function returns the first non-null value from the list.
We will use the demo_table for the demonstration.
Syntax:
SELECT * FROM table_name ORDER BY COALESCE(column_name, (select max(column_name) from table_name)+1);
In this query COALESCE make NULL values of the column to return (1+maximum value) of the column, hence making it the maximum value to be returned at last while sorting in ascending order.
Query:
SELECT * FROM demo_table ORDER BY COALESCE(AGE, (select max(AGE) from demo_table)+1);
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...