ORDER BY multiple columns in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ORDER BY with two columns

Given the table Employees with columns Department and Salary, what is the output order of this query?

SELECT Department, Salary FROM Employees ORDER BY Department ASC, Salary DESC;

SQL

CREATE TABLE Employees (Department VARCHAR(20), Salary INT);
INSERT INTO Employees VALUES
('Sales', 5000),
('Sales', 7000),
('HR', 6000),
('HR', 5500),
('IT', 8000);

SELECT Department, Salary FROM Employees ORDER BY Department ASC, Salary DESC;

A [('HR', 6000), ('HR', 5500), ('IT', 8000), ('Sales', 7000), ('Sales', 5000)]

B [('Sales', 7000), ('Sales', 5000), ('HR', 6000), ('HR', 5500), ('IT', 8000)]

C [('HR', 5500), ('HR', 6000), ('IT', 8000), ('Sales', 5000), ('Sales', 7000)]

D [('IT', 8000), ('HR', 6000), ('HR', 5500), ('Sales', 7000), ('Sales', 5000)]

💡 Hint

ORDER BY sorts first by the first column, then by the second within each group.

The query sorts rows first by Department alphabetically ascending: HR, IT, Sales. Within each Department, Salary is sorted descending. So HR salaries 6000 then 5500, IT only one row 8000, Sales 7000 then 5000.

🧠 Conceptual

intermediate

Understanding ORDER BY with NULL values

Consider a table Products with columns Category and Price. If you run:

SELECT Category, Price FROM Products ORDER BY Category ASC, Price DESC;

and some Price values are NULL, where will those NULLs appear in the sorted result?

A NULL prices appear first within each Category when ordering Price DESC.

B NULL prices appear last within each Category when ordering Price DESC.

C NULL prices are ignored and do not appear in the result.

D NULL prices cause an error in ORDER BY.

💡 Hint

Think about how NULLs are treated in descending order sorting.

In SQL, NULLs are treated as unknown and usually appear last when sorting descending. So within each Category, rows with NULL Price come after rows with actual prices.

📝 Syntax

advanced

Identify the syntax error in ORDER BY multiple columns

Which option contains a syntax error in the ORDER BY clause when sorting by two columns Age ascending and Name descending?

A ORDER BY Age ASC, Name DESC

B ORDER BY Age, Name DESC

C ORDER BY Age ASC; Name DESC

D ORDER BY Age ASC Name DESC

💡 Hint

Check the punctuation between columns in ORDER BY.

Option D is missing a comma between 'Age ASC' and 'Name DESC', causing a syntax error. Option D has a semicolon which ends the statement early, also invalid but not in ORDER BY clause itself.

❓ optimization

advanced

Optimizing ORDER BY with multiple columns

You have a large table Sales with columns Region, SalesDate, and Amount. You often run:

SELECT * FROM Sales ORDER BY Region ASC, SalesDate DESC;

Which index will best improve the performance of this query?

A CREATE INDEX idx_region ON Sales(Region);

B CREATE INDEX idx_salesdate_region ON Sales(SalesDate DESC, Region ASC);

C CREATE INDEX idx_region_salesdate ON Sales(Region ASC, SalesDate DESC);

D CREATE INDEX idx_amount ON Sales(Amount);

💡 Hint

Indexes should match the ORDER BY column order and directions.

An index on (Region ASC, SalesDate DESC) matches the ORDER BY clause exactly, allowing the database to efficiently retrieve rows in the desired order without extra sorting.

🔧 Debug

expert

Why does this ORDER BY query return unexpected results?

You run this query:

SELECT Name, Score FROM Players ORDER BY Score, Name;

but the results are not sorted as expected by Score ascending and then Name ascending. What is the most likely cause?

A The Score column is stored as text, so sorting is lexicographical, not numeric.

B The query is missing ASC keywords, so sorting defaults to descending.

C ORDER BY cannot sort by multiple columns without parentheses.

D The database does not support ORDER BY with multiple columns.

💡 Hint

Check the data type of the columns used in ORDER BY.

If Score is stored as text, sorting will be alphabetical (e.g., '10' before '2'), causing unexpected order. Numeric sorting requires Score to be a numeric type.

Practice

1.

What does the SQL clause ORDER BY column1, column2 do?

easy

A. Filters rows where column1 equals column2

B. Sorts the results only by column2

C. Sorts the results first by column1, then by column2 if there are ties in column1

D. Groups rows by column1 and column2

Solution

1. Step 1: Understand ORDER BY with multiple columns

   The ORDER BY clause sorts rows by the first column listed.

2. Step 2: Sorting with tie-breaker column

   If two or more rows have the same value in the first column, sorting continues by the second column.

3. Final Answer:

   Sorts the results first by column1, then by column2 if there are ties in column1 -> Option C

4. Quick Check:

   ORDER BY multiple columns sorts stepwise [OK]

Hint: ORDER BY sorts by first column, then next for ties [OK]

Common Mistakes:

• Thinking ORDER BY filters rows
• Assuming it groups data
• Believing it sorts only by the last column

2.

Which of the following is the correct syntax to sort by age ascending and then by name descending?

SELECT * FROM users ORDER BY ...

easy

A. age ASC, name DESC

B. age DESC, name ASC

C. ORDER BY age DESC, name

D. age, name ASC

Solution

1. Step 1: Specify sorting order for each column

   Use ASC for ascending and DESC for descending explicitly after each column.

2. Step 2: Apply correct order for age and name

   Since age is ascending and name is descending, write age ASC, name DESC.

3. Final Answer:

   age ASC, name DESC -> Option A

4. Quick Check:

   Correct syntax uses ASC and DESC per column [OK]

Hint: Use ASC or DESC after each column in ORDER BY [OK]

Common Mistakes:

• Omitting ASC or DESC and expecting default
• Mixing order keywords incorrectly
• Using ORDER BY twice in one query

3.

Given the table products with columns category and price, what will this query return?

SELECT category, price FROM products ORDER BY category ASC, price DESC;

medium

A. Rows sorted by category alphabetically, then by price highest to lowest within each category

B. Rows sorted by price descending only

C. Rows sorted by category descending, then price ascending

D. Rows grouped by category and price

Solution

1. Step 1: Analyze ORDER BY clause

   The query orders rows first by category ascending (A to Z).

2. Step 2: Understand secondary sorting

   Within each category, rows are sorted by price descending (highest to lowest).

3. Final Answer:

   Rows sorted by category alphabetically, then by price highest to lowest within each category -> Option A

4. Quick Check:

   ORDER BY sorts stepwise by listed columns [OK]

Hint: First column sorts main order, second breaks ties [OK]

Common Mistakes:

• Confusing ascending and descending order
• Thinking ORDER BY groups rows
• Ignoring second column sorting

4.

Identify the error in this query:

SELECT * FROM employees ORDER BY salary DESC,;

medium

A. Missing ASC or DESC after salary

B. Extra comma after DESC causes syntax error

C. ORDER BY cannot have multiple columns

D. salary is not a valid column

Solution

1. Step 1: Check ORDER BY syntax

   The ORDER BY clause lists columns separated by commas without trailing commas.

2. Step 2: Identify trailing comma error

   The comma after DESC is extra and causes a syntax error.

3. Final Answer:

   Extra comma after DESC causes syntax error -> Option B

4. Quick Check:

   Trailing commas in ORDER BY cause syntax errors [OK]

Hint: No comma after last ORDER BY column [OK]

Common Mistakes:

• Leaving trailing commas in ORDER BY
• Forgetting ASC or DESC (defaults to ASC though)
• Assuming ORDER BY accepts invalid columns

5.

You have a students table with columns grade (integer) and last_name (text). You want to list students sorted by grade descending, but for students with the same grade, sort by last name ascending. Which query achieves this?

hard

A. SELECT * FROM students ORDER BY grade DESC last_name ASC;

B. SELECT * FROM students ORDER BY last_name ASC, grade DESC;

C. SELECT * FROM students ORDER BY grade ASC, last_name DESC;

D. SELECT * FROM students ORDER BY grade DESC, last_name ASC;

Solution

1. Step 1: Determine primary sorting column and order

   Primary sort is by grade descending (highest grade first).

2. Step 2: Determine secondary sorting column and order

   For ties in grade, sort by last_name ascending (A to Z).

3. Step 3: Write correct ORDER BY syntax

   Use ORDER BY grade DESC, last_name ASC with comma separating columns.

4. Final Answer:

   SELECT * FROM students ORDER BY grade DESC, last_name ASC; -> Option D

5. Quick Check:

   ORDER BY multiple columns with specified directions [OK]

Hint: List columns in ORDER BY with desired ASC/DESC per column [OK]

Common Mistakes:

• Swapping order of columns in ORDER BY
• Missing comma between columns
• Omitting ASC or DESC keywords