Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!