ORDER BY with NULLS FIRST and NULLS LAST in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ORDER BY with NULLS FIRST

Given the table employees with a column salary containing some NULL values, what is the output order of salaries for the query:

SELECT salary FROM employees ORDER BY salary NULLS FIRST;

Assuming salaries are: 50000, NULL, 70000, NULL, 60000.

PostgreSQL

CREATE TEMP TABLE employees (salary INTEGER);
INSERT INTO employees VALUES (50000), (NULL), (70000), (NULL), (60000);
SELECT salary FROM employees ORDER BY salary NULLS FIRST;

A [50000, 60000, 70000, NULL, NULL]

B [NULL, NULL, 50000, 60000, 70000]

C [70000, 60000, 50000, NULL, NULL]

D [NULL, 50000, NULL, 60000, 70000]

💡 Hint

Remember that NULLS FIRST places all NULL values before any non-NULL values in the order.

The ORDER BY clause with NULLS FIRST sorts all NULL values before any non-NULL values. Then the non-NULL values are sorted ascending by default.

❓ query_result

intermediate

Output of ORDER BY with NULLS LAST

Given the table products with a column price containing some NULL values, what is the output order of prices for the query:

SELECT price FROM products ORDER BY price NULLS LAST;

Assuming prices are: 100, NULL, 50, NULL, 75.

PostgreSQL

CREATE TEMP TABLE products (price INTEGER);
INSERT INTO products VALUES (100), (NULL), (50), (NULL), (75);
SELECT price FROM products ORDER BY price NULLS LAST;

A [NULL, NULL, 50, 75, 100]

B [50, NULL, 75, 100, NULL]

C [50, 75, 100, NULL, NULL]

D [100, 75, 50, NULL, NULL]

💡 Hint

NULLS LAST places all NULL values after the non-NULL values in the order.

The ORDER BY clause with NULLS LAST sorts all non-NULL values first in ascending order, then places NULL values at the end.

📝 Syntax

advanced

Identify the syntax error in ORDER BY with NULLS FIRST

Which of the following queries will cause a syntax error in PostgreSQL?

A SELECT name FROM users ORDER BY age DESC NULLS FIRST;

B SELECT name FROM users ORDER BY age NULLS FIRST;

C SELECT name FROM users ORDER BY age NULLS LAST;

D SELECT name FROM users ORDER BY age FIRST NULLS;

💡 Hint

Check the correct order of keywords in the ORDER BY clause for NULLS placement.

The correct syntax is 'ORDER BY column [ASC|DESC] NULLS {FIRST|LAST}'. Option D reverses NULLS and FIRST causing a syntax error.

❓ query_result

advanced

Effect of ORDER BY DESC NULLS FIRST

Given the table scores with column points containing values: 10, NULL, 20, NULL, 15, what is the output order of points for the query:

SELECT points FROM scores ORDER BY points DESC NULLS FIRST;

PostgreSQL

CREATE TEMP TABLE scores (points INTEGER);
INSERT INTO scores VALUES (10), (NULL), (20), (NULL), (15);
SELECT points FROM scores ORDER BY points DESC NULLS FIRST;

A [NULL, NULL, 20, 15, 10]

B [20, 15, 10, NULL, NULL]

C [NULL, 20, NULL, 15, 10]

D [10, 15, 20, NULL, NULL]

💡 Hint

NULLS FIRST places NULLs before non-NULLs even when sorting descending.

The query orders NULL values first, then non-NULL values in descending order: NULL, NULL, 20, 15, 10.

🧠 Conceptual

expert

Why use NULLS FIRST or NULLS LAST in ORDER BY?

Which of the following best explains why you might want to use NULLS FIRST or NULLS LAST in an ORDER BY clause?

A To control whether NULL values appear at the beginning or end of the sorted result, because by default NULLs sort differently depending on ASC or DESC.

B To automatically remove rows with NULL values from the result set during sorting.

C To convert NULL values into zeros before sorting, so they don't affect the order.

D To force the database to ignore NULL values and only sort non-NULL values.

💡 Hint

Think about how NULL values behave in sorting and why you might want to control their position.

NULLS FIRST and NULLS LAST let you decide if NULL values appear at the start or end of the sorted list. By default, NULLs sort last in ASC and first in DESC, which can be confusing.