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

Need a 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

Need a 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

Need a 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

Need a hint?

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