Often in PostgreSQL you may want to use the ORDER BY statement to sort rows based on values in a particular column and ensure that NULL values appear last.
If you order the values in ascending order (ASC) then NULL values appear last by default.
However, if you order the value in descending order (DESC) then NULL values appear first by default.
In order to sort in descending order and ensure that NULL values appear last, you can use the NULLS LAST option as follows:
SELECT *
FROM athletes
ORDER BY points DESC NULLS LAST;
This particular example orders the rows of the athletes table based on the values in the points column in descending order and specifies that NULL values should appear last.
Note: You can also specify NULLS FIRST to instead make NULL values appear first when sorting.
The following example shows how to use this syntax in practice.
Example: How to ORDER BY with NULL Values Last in PostgreSQL
Suppose that we create the following table named athletes that contains information about points scored by various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Celtics', NULL);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Nuggets', NULL);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+---------+--------
1 | Mavs | 22
2 | Hawks | 30
3 | Kings | 12
4 | Kings | 25
5 | Mavs | 31
6 | Mavs | 16
7 | Celtics |
8 | Celtics | 14
9 | Nuggets |
10 | Hawks | 26
Suppose that we would like to order the rows of the athletes table based on the values in the points column in descending order.
Suppose we attempt to use the following syntax to do so:
SELECT *
FROM athletes
ORDER BY points DESC;
id | team | points
----+---------+--------
7 | Celtics |
9 | Nuggets |
5 | Mavs | 31
2 | Hawks | 30
10 | Hawks | 26
4 | Kings | 25
1 | Mavs | 22
6 | Mavs | 16
8 | Celtics | 14
3 | Kings | 12
Notice that the rows are sorted in descending order based on values in the points column but the NULL values appear first in the column.
To instead make the NULL values appear last, we can specify NULLS LAST as follows:
SELECT *
FROM athletes
ORDER BY points DESC NULLS LAST;
id | team | points
----+---------+--------
5 | Mavs | 31
2 | Hawks | 30
10 | Hawks | 26
4 | Kings | 25
1 | Mavs | 22
6 | Mavs | 16
8 | Celtics | 14
3 | Kings | 12
7 | Celtics |
9 | Nuggets |
Notice that the rows are now sorted in descending order based on values in the points column but the NULL values appear last in the column.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: ORDER BY Multiple Values in Specific Order
PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
