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

Challenge - 5 Problems

❓ query_result

intermediate

What is the output order of this query?

Consider the table Employees with columns Department and Salary. What will be the order of rows returned by this query?

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

MySQL

CREATE TABLE Employees (Name VARCHAR(20), Department VARCHAR(20), Salary INT);
INSERT INTO Employees VALUES
('Alice', 'Sales', 5000),
('Bob', 'Sales', 6000),
('Charlie', 'HR', 5500),
('David', 'HR', 5200);

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

A David (HR, 5200), Charlie (HR, 5500), Alice (Sales, 5000), Bob (Sales, 6000)

B Charlie (HR, 5500), David (HR, 5200), Bob (Sales, 6000), Alice (Sales, 5000)

C Alice (Sales, 5000), Bob (Sales, 6000), Charlie (HR, 5500), David (HR, 5200)

D Bob (Sales, 6000), Alice (Sales, 5000), David (HR, 5200), Charlie (HR, 5500)

💡 Hint

Rows are sorted first by Department alphabetically, then by Salary in descending order within each department.

The query orders rows by Department ascending (HR before Sales). Within each department, rows are ordered by Salary descending. So HR rows come first with Charlie (5500) before David (5200), then Sales rows with Bob (6000) before Alice (5000).

🧠 Conceptual

intermediate

How does ORDER BY multiple columns work?

When you use ORDER BY with multiple columns, how does the database sort the rows?

A It sorts rows by the first column, then breaks ties by the second column, and so on.

B It sorts rows only by the last column listed.

C It sorts rows by all columns simultaneously without priority.

D It randomly picks one column to sort by.

💡 Hint

Think about sorting a list of people by last name, then first name.

The database sorts rows first by the first column listed in ORDER BY. If two rows have the same value in that column, it uses the second column to decide their order, and so on for additional columns.

📝 Syntax

advanced

Which query correctly orders by two columns with different directions?

Choose the correct SQL query that orders Products by Category ascending and Price descending.

A SELECT * FROM Products ORDER BY Category ASCENDING, Price DESC;

B SELECT * FROM Products ORDER BY Category, Price DESCENDING;

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

D SELECT * FROM Products ORDER BY Category ASC, Price DESCENDING;

💡 Hint

Check the correct keywords for ascending and descending order in SQL.

The correct keywords are ASC for ascending and DESC for descending. The other options use invalid keywords like DESCENDING or ASCENDING.

❓ optimization

advanced

How can indexing improve ORDER BY multiple columns?

You have a large table Orders with columns CustomerID and OrderDate. You often run:

SELECT * FROM Orders ORDER BY CustomerID ASC, OrderDate DESC;

Which index will best improve this query's performance?

A CREATE INDEX idx_customer_order ON Orders(CustomerID ASC, OrderDate DESC);

B CREATE INDEX idx_order ON Orders(OrderDate);

C CREATE INDEX idx_customer ON Orders(CustomerID);

D CREATE INDEX idx_order_customer ON Orders(OrderDate DESC, CustomerID ASC);

💡 Hint

Indexes should match the order of columns in the ORDER BY clause.

An index on (CustomerID ASC, OrderDate DESC) matches the ORDER BY clause exactly, allowing the database to retrieve rows in the desired order efficiently. Other indexes either have columns in wrong order or only cover one column.

🔧 Debug

expert

Why does this ORDER BY query return unexpected results?

Given the table Students with columns Name, Grade, and Age, a developer runs:

SELECT * FROM Students ORDER BY Grade, Age DESC;

But the results are not sorted by Grade ascending as expected. What is the most likely cause?

A The database ignores the first column in ORDER BY if the second column has DESC.

B The query syntax is invalid because <code>Age DESC</code> cannot be used with <code>Grade</code>.

C The table has no data, so sorting does nothing.

D The <code>Grade</code> column contains NULL values which are sorted first by default.

💡 Hint

Think about how NULL values affect sorting order.

By default, NULL values in Grade appear first when sorting ascending, which can make it seem like sorting is incorrect. The syntax is valid, and the database does not ignore columns in ORDER BY. The table having no data would return no rows, not unexpected order.

Practice

1. What does the ORDER BY clause with multiple columns do in a MySQL query?

easy

A. Sorts the result set by the first column, then by the second column if there are ties.

B. Filters rows based on multiple column values.

C. Joins tables using multiple columns.

D. Groups rows by multiple columns.

Solution

1. Step 1: Understand the purpose of ORDER BY with multiple columns

   The ORDER BY clause sorts rows in the result set. When multiple columns are listed, sorting happens first by the first column.

2. Step 2: Recognize tie-breaking with the second column

   If two or more rows have the same value in the first column, the second column is used to sort those tied rows.

3. Final Answer:

   Sorts the result set by the first column, then by the second column if there are ties. -> Option A

4. Quick Check:

   ORDER BY multiple columns sorts stepwise [OK]

Hint: Think of sorting like sorting by last name, then first name [OK]

Common Mistakes:

• Confusing ORDER BY with filtering (WHERE clause)
• Thinking ORDER BY changes data, not just order
• Assuming ORDER BY groups rows

2. Which of the following is the correct syntax to order a table by age ascending and then by name descending in MySQL?

easy

A. ORDER BY age, name DESCENDING

B. ORDER BY age ASC, name DESC

C. ORDER BY age DESC, name ASC

D. ORDER BY age ASCENDING, name DESCENDING

Solution

1. Step 1: Recall correct keywords for sorting order

   MySQL uses ASC for ascending and DESC for descending order.

2. Step 2: Check syntax for multiple columns

   Multiple columns are separated by commas, each followed optionally by ASC or DESC.

3. Final Answer:

   ORDER BY age ASC, name DESC -> Option B

4. Quick Check:

   ASC and DESC keywords are short and exact [OK]

Hint: Use ASC for ascending, DESC for descending exactly [OK]

Common Mistakes:

• Using DESCENDING instead of DESC
• Mixing ASC and DESC keywords incorrectly
• Omitting commas between columns

3. Given the table Employees with columns department and salary, what will the result order be for this query?

SELECT * FROM Employees ORDER BY department ASC, salary DESC;

medium

A. Rows sorted by salary descending, then department ascending.

B. Rows sorted randomly because ORDER BY is incorrect.

C. Rows sorted by department descending, then salary ascending.

D. Rows sorted by department alphabetically, then highest salary first within each department.

Solution

1. Step 1: Analyze the ORDER BY clause

   The query orders rows first by department in ascending (alphabetical) order.

2. Step 2: Understand secondary sorting by salary

   Within each department group, rows are sorted by salary in descending order (highest salary first).

3. Final Answer:

   Rows sorted by department alphabetically, then highest salary first within each department. -> Option D

4. Quick Check:

   ORDER BY column1 ASC, column2 DESC sorts stepwise [OK]

Hint: First column sorts main groups, second column sorts inside groups [OK]

Common Mistakes:

• Reversing the order of columns in ORDER BY
• Confusing ASC and DESC directions
• Assuming ORDER BY sorts only by one column

4. Identify the error in this MySQL query:

SELECT * FROM Products ORDER BY price ASC name DESC;

medium

A. Missing comma between price ASC and name DESC.

B. Incorrect use of ASC and DESC keywords.

C. ORDER BY cannot have more than one column.

D. SELECT * cannot be used with ORDER BY.

Solution

1. Step 1: Check syntax for multiple columns in ORDER BY

   Multiple columns must be separated by commas in the ORDER BY clause.

2. Step 2: Identify missing comma

   The query misses a comma between price ASC and name DESC, causing syntax error.

3. Final Answer:

   Missing comma between price ASC and name DESC. -> Option A

4. Quick Check:

   Separate columns with commas in ORDER BY [OK]

Hint: Always put commas between columns in ORDER BY [OK]

Common Mistakes:

• Forgetting commas between columns
• Misplacing ASC/DESC keywords
• Thinking ORDER BY limits to one column

5. You have a table Students with columns grade (integer), last_name, and first_name. You want to list students sorted by grade descending, then by last name ascending, and finally by first name ascending. Which query achieves this?

hard

A. SELECT * FROM Students ORDER BY last_name ASC, first_name ASC, grade DESC;

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

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

D. SELECT * FROM Students ORDER BY grade DESC first_name ASC last_name ASC;

Solution

1. Step 1: Understand sorting priorities

   The primary sort is by grade descending, so highest grades come first.

2. Step 2: Apply secondary and tertiary sorting

   Within the same grade, sort by last_name ascending (A to Z), then by first_name ascending.

3. Step 3: Check syntax correctness

   SELECT * FROM Students ORDER BY grade DESC, last_name ASC, first_name ASC; uses correct syntax with commas separating columns and proper ASC/DESC keywords.

4. Final Answer:

   SELECT * FROM Students ORDER BY grade DESC, last_name ASC, first_name ASC; -> Option C

5. Quick Check:

   Order by multiple columns with correct directions and commas [OK]

Hint: List columns in ORDER BY with commas and correct ASC/DESC [OK]

Common Mistakes:

• Wrong order of columns in ORDER BY
• Missing commas between columns
• Mixing ASC and DESC incorrectly
• Omitting ASC/DESC keywords