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.

Practice

1. What does ORDER BY column_name NULLS FIRST do in a PostgreSQL query?

easy

A. It ignores NULL values while sorting by column_name.

B. It sorts the rows by column_name and places NULL values at the end.

C. It causes a syntax error because NULLS FIRST is not valid.

D. It sorts the rows by column_name and places NULL values at the beginning.

Solution

1. Step 1: Understand ORDER BY behavior with NULLS FIRST

   The ORDER BY clause sorts rows by the specified column. Adding NULLS FIRST means any NULL values in that column appear before non-NULL values.

2. Step 2: Confirm the effect on sorting

   So, the rows with NULL in column_name come first, then the rest sorted normally.

3. Final Answer:

   It sorts the rows by column_name and places NULL values at the beginning. -> Option D

4. Quick Check:

   NULLS FIRST means NULLs appear first [OK]

Hint: NULLS FIRST puts empty values at the top of the list [OK]

Common Mistakes:

• Confusing NULLS FIRST with NULLS LAST
• Thinking NULLs are ignored in sorting
• Assuming NULLS FIRST causes errors

2. Which of the following is the correct syntax to sort a table by column score in ascending order with NULL values last?

easy

A. SELECT * FROM table ORDER BY score ASC NULLS LAST;

B. SELECT * FROM table ORDER BY score NULLS LAST ASC;

C. SELECT * FROM table ORDER BY NULLS LAST score ASC;

D. SELECT * FROM table ORDER BY score ASC LAST NULLS;

Solution

1. Step 1: Recall correct ORDER BY syntax with NULLS LAST

   The correct syntax places the column name first, then the sort order (ASC or DESC), followed by NULLS FIRST or NULLS LAST.

2. Step 2: Check each option

   SELECT * FROM table ORDER BY score ASC NULLS LAST; follows this order: score ASC NULLS LAST. Others mix the order or use invalid syntax.

3. Final Answer:

   SELECT * FROM table ORDER BY score ASC NULLS LAST; -> Option A

4. Quick Check:

   Column, order, then NULLS position [OK]

Hint: Order: column, ASC/DESC, then NULLS FIRST/LAST [OK]

Common Mistakes:

• Placing NULLS FIRST/LAST before column name
• Swapping ASC/DESC and NULLS keywords
• Using invalid keyword order causing syntax errors

3. Given the table students with column grade containing values (NULL, 85, 90, NULL, 75), what is the order of grade values after running:
SELECT grade FROM students ORDER BY grade DESC NULLS FIRST;?

medium

A. [NULL, NULL, 90, 85, 75]

B. [90, 85, 75, NULL, NULL]

C. [75, 85, 90, NULL, NULL]

D. [NULL, 90, 85, 75, NULL]

Solution

1. Step 1: Understand DESC with NULLS FIRST

   Sorting by grade DESC means highest grades first. Adding NULLS FIRST places NULL values before all grades.

2. Step 2: Arrange values accordingly

   NULLs come first: [NULL, NULL], then grades in descending order: 90, 85, 75.

3. Final Answer:

   [NULL, NULL, 90, 85, 75] -> Option A

4. Quick Check:

   DESC sorts high to low, NULLS FIRST puts NULLs upfront [OK]

Hint: NULLS FIRST always puts NULLs before sorted values [OK]

Common Mistakes:

• Placing NULLs at the end despite NULLS FIRST
• Sorting grades ascending instead of descending
• Mixing order of NULLs and values

4. You wrote this query but it gives a syntax error:
SELECT * FROM employees ORDER BY salary NULLS FIRST DESC;
What is the error and how to fix it?

medium

A. The column name salary is missing ASC or DESC.

B. NULLS FIRST cannot be used with DESC; remove NULLS FIRST.

C. The order of NULLS FIRST and DESC is wrong; use ORDER BY salary DESC NULLS FIRST.

D. You must use parentheses around NULLS FIRST and DESC.

Solution

1. Step 1: Identify correct keyword order in ORDER BY

   The correct syntax is: column name, then ASC or DESC, then NULLS FIRST or NULLS LAST.

2. Step 2: Fix the query

   Change ORDER BY salary NULLS FIRST DESC to ORDER BY salary DESC NULLS FIRST to fix the syntax error.

3. Final Answer:

   The order of NULLS FIRST and DESC is wrong; use ORDER BY salary DESC NULLS FIRST. -> Option C

4. Quick Check:

   Column, order, then NULLS position [OK]

Hint: Put ASC/DESC before NULLS FIRST/LAST [OK]

Common Mistakes:

• Placing NULLS FIRST before ASC/DESC
• Assuming NULLS FIRST is invalid with DESC
• Missing ASC/DESC keyword

5. You have a table products with a price column containing some NULLs. You want to list products sorted by price ascending, but show NULL prices at the end. Then, for products with the same price, sort by name ascending. Which query achieves this?

hard

A. SELECT * FROM products ORDER BY price NULLS LAST ASC, name ASC;

B. SELECT * FROM products ORDER BY price ASC NULLS LAST, name ASC;

C. SELECT * FROM products ORDER BY price ASC, name ASC NULLS LAST;

D. SELECT * FROM products ORDER BY price ASC NULLS FIRST, name ASC;

Solution

1. Step 1: Sort by price ascending with NULLs last

   Use ORDER BY price ASC NULLS LAST to sort prices low to high and put NULLs at the end.

2. Step 2: Add secondary sort by name ascending

   After sorting by price, add name ASC to sort products with the same price alphabetically.

3. Final Answer:

   SELECT * FROM products ORDER BY price ASC NULLS LAST, name ASC; -> Option B

4. Quick Check:

   Price ASC NULLS LAST, then name ASC [OK]

Hint: Chain ORDER BY: main column with NULLS, then tie-breaker column [OK]

Common Mistakes:

• Placing NULLS LAST before ASC
• Applying NULLS LAST to wrong column
• Using NULLS FIRST instead of NULLS LAST