CASE in ORDER BY in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CASE in ORDER BY with simple conditions

Consider a table Employees with columns id, name, and department. What is the output order of names after running this query?

SELECT name, department FROM Employees ORDER BY CASE department WHEN 'HR' THEN 1 WHEN 'Sales' THEN 2 ELSE 3 END;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(20), department VARCHAR(20));
INSERT INTO Employees VALUES (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Engineering'), (4, 'Diana', 'Sales'), (5, 'Eve', 'HR');

SELECT name, department FROM Employees ORDER BY CASE department WHEN 'HR' THEN 1 WHEN 'Sales' THEN 2 ELSE 3 END;

A [('Bob', 'HR'), ('Alice', 'Sales'), ('Diana', 'Sales'), ('Eve', 'HR'), ('Charlie', 'Engineering')]

B [('Alice', 'Sales'), ('Diana', 'Sales'), ('Bob', 'HR'), ('Eve', 'HR'), ('Charlie', 'Engineering')]

C [('Charlie', 'Engineering'), ('Bob', 'HR'), ('Eve', 'HR'), ('Alice', 'Sales'), ('Diana', 'Sales')]

D [('Bob', 'HR'), ('Eve', 'HR'), ('Alice', 'Sales'), ('Diana', 'Sales'), ('Charlie', 'Engineering')]

💡 Hint

Think about how the CASE expression assigns numeric values to departments for sorting.

The CASE expression assigns 1 to 'HR', 2 to 'Sales', and 3 to others. ORDER BY sorts ascending by these values, so HR employees come first, then Sales, then Engineering.

📝 Syntax

intermediate

Identify the syntax error in CASE used in ORDER BY

Which option contains a syntax error when using CASE in ORDER BY clause?

SQL

SELECT id, name FROM Employees ORDER BY CASE WHEN department = 'HR' THEN 1 ELSE 2 END;

A ORDER BY CASE WHEN department = 'HR' THEN 1 ELSE END

B ORDER BY CASE department WHEN 'HR' THEN 1 ELSE 2 END

C ORDER BY CASE WHEN department = 'HR' THEN 1 ELSE 2 END

D ORDER BY CASE department WHEN 'HR' THEN 1 ELSE 2 END ASC

💡 Hint

Look for incomplete CASE expressions.

Option A is missing the value after ELSE, causing a syntax error. All others are valid CASE expressions.

❓ optimization

advanced

Optimizing ORDER BY CASE for performance

Given a large table Orders with a status column having values 'Pending', 'Shipped', 'Cancelled', which ORDER BY clause is most efficient to sort rows with 'Pending' first, then 'Shipped', then 'Cancelled'?

A ORDER BY CASE status WHEN 'Pending' THEN 1 WHEN 'Shipped' THEN 2 ELSE 3 END

B ORDER BY status = 'Pending' DESC, status = 'Shipped' DESC, status = 'Cancelled' DESC

C ORDER BY FIELD(status, 'Pending', 'Shipped', 'Cancelled')

D ORDER BY CASE WHEN status = 'Pending' THEN 1 WHEN status = 'Shipped' THEN 2 ELSE 3 END

💡 Hint

Consider built-in functions optimized for sorting by specific values.

The FIELD function (MySQL) is optimized for sorting by specific values and can be faster than CASE expressions. Options A and D are similar CASE expressions. Option C uses boolean expressions which may be less efficient.

❓ query_result

advanced

Result of ORDER BY CASE with multiple conditions

Given a table Products with columns id, category, and price, what is the order of id after running this query?

SELECT id FROM Products ORDER BY CASE WHEN category = 'Electronics' AND price > 1000 THEN 1 WHEN category = 'Electronics' THEN 2 ELSE 3 END, price DESC;

SQL

CREATE TABLE Products (id INT, category VARCHAR(20), price INT);
INSERT INTO Products VALUES (1, 'Electronics', 1500), (2, 'Electronics', 800), (3, 'Clothing', 500), (4, 'Electronics', 1200), (5, 'Clothing', 700);

SELECT id FROM Products ORDER BY CASE WHEN category = 'Electronics' AND price > 1000 THEN 1 WHEN category = 'Electronics' THEN 2 ELSE 3 END, price DESC;

A [1, 4, 2, 5, 3]

B [4, 1, 2, 3, 5]

C [2, 1, 4, 3, 5]

D [1, 4, 2, 3, 5]

💡 Hint

First order by CASE result, then by price descending within groups.

Rows with category 'Electronics' and price > 1000 get 1, then other 'Electronics' get 2, others get 3. Within each group, order by price descending. So ids 1 and 4 (both Electronics > 1000) come first ordered by price desc (1500 then 1200), then id 2 (Electronics 800), then clothing ids 3 and 5 ordered by price desc (700 then 500).

🔧 Debug

expert

Debug unexpected ORDER BY CASE behavior

A developer wrote this query to order customers by priority:

SELECT customer_id, priority FROM Customers ORDER BY CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END DESC;

But the results show 'Low' priority customers first. What is the cause?

A The CASE expression is missing ELSE clause causing NULLs to be sorted first.

B The DESC applies to the entire CASE result, reversing the order so 3 comes first, showing 'Low' priority first.

C The priority column has trailing spaces causing CASE to fail matching values.

D The query needs GROUP BY to order correctly by priority.

💡 Hint

Think about how DESC affects numeric values from CASE.

The CASE assigns 1 to 'High', 2 to 'Medium', 3 to others. ORDER BY ... DESC reverses order, so 3 (Low) comes first, then 2, then 1. This causes 'Low' priority customers to appear first.