How to Filter Null Values to Last While Sorting Ascending in SQL?

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:

My Personal Notes arrow_drop_up

Save