ORDER BY multiple columns in SQL - Mini Project: Build & Apply

Sorting Customer Orders by Date and Amount

📖 Scenario: You work for an online store. You have a table of customer orders. Each order has a date and a total amount. You want to see the orders sorted first by the date they were placed, and then by the amount spent.

🎯 Goal: Create an SQL query that sorts the orders by order_date in ascending order, and if two orders have the same date, sort those by total_amount in descending order.

📋 What You'll Learn

Create a table called orders with columns order_id (integer), order_date (date), and total_amount (decimal).

Insert exactly three orders with these values: (1, '2024-01-10', 150.00), (2, '2024-01-10', 200.00), (3, '2024-01-09', 100.00).

Write a SELECT query to get all columns from orders.

Use ORDER BY to sort by order_date ascending and then total_amount descending.

💡 Why This Matters

🌍 Real World

Sorting data by multiple columns is common in reports, dashboards, and data analysis to organize information clearly.

💼 Career

Database developers and analysts often write queries with ORDER BY multiple columns to prepare data for business decisions.

1

Create the orders table and insert data

Create a table called orders with columns order_id as integer, order_date as date, and total_amount as decimal. Then insert these three rows exactly: (1, '2024-01-10', 150.00), (2, '2024-01-10', 200.00), and (3, '2024-01-09', 100.00).

SQL

-- Create the orders table and insert the three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Write a basic SELECT query

Write a SELECT query to get all columns from the orders table.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, order_date, total_amount) VALUES
(1, '2024-01-10', 150.00),
(2, '2024-01-10', 200.00),
(3, '2024-01-09', 100.00);

-- Write SELECT query to get all columns from orders
-- Your code here

Hint

Use SELECT * FROM orders; to get all columns.

3

Add ORDER BY for order_date ascending

Modify the SELECT query to sort the results by order_date in ascending order using ORDER BY order_date ASC.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, order_date, total_amount) VALUES
(1, '2024-01-10', 150.00),
(2, '2024-01-10', 200.00),
(3, '2024-01-09', 100.00);

SELECT * FROM orders
-- Add ORDER BY to sort by order_date ascending
-- Your code here

Hint

Use ORDER BY order_date ASC to sort by date from earliest to latest.

4

Add second column to ORDER BY for total_amount descending

Modify the SELECT query to sort by order_date ascending and then by total_amount descending using ORDER BY order_date ASC, total_amount DESC.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, order_date, total_amount) VALUES
(1, '2024-01-10', 150.00),
(2, '2024-01-10', 200.00),
(3, '2024-01-09', 100.00);

SELECT * FROM orders
-- Add second column to ORDER BY to sort total_amount descending
-- Your code here

Hint

Use ORDER BY order_date ASC, total_amount DESC to sort by date first, then amount highest to lowest.

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