Courses/SQL/ORDER BY & Sorting

Lesson 5 • Beginner Track

ORDER BY & Sorting

By the end of this lesson you'll be able to put query results in exactly the order you want — cheapest to dearest, A to Z, newest first — break ties with a second column, sort by a calculated value, and pull the "top 3" of anything by pairing ORDER BY with LIMIT. Sorting turns a raw pile of rows into an answer.

What You'll Learn

✓Sort rows with ORDER BY (ascending is the default)
✓Flip the order with DESC (highest / Z→A first)
✓Break ties by sorting on a second column
✓Sort by an alias or a calculated expression
✓Understand where NULL (missing) values land
✓Get the top-N of anything with ORDER BY + LIMIT

Before you start: finish WHERE Clause & Filtering so you're comfortable choosing which rows come back — this lesson is about the order they come back in. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the expected result so you can check yourself.

Our Sample Table: `products`

Every query in this lesson runs against this same little products table — the one you met in the SELECT lesson. Notice the prices and stock levels; you'll be sorting by them in a moment.

Result:

id	product_name	category	price	stock
1	Wireless Mouse	Electronics	24.99	120
2	Coffee Mug	Kitchen	9.5	300
3	Mechanical Keyboard	Electronics	79	45
4	Notebook	Stationery	3.25	500
5	Desk Lamp	Home	32	80
6	USB-C Cable	Electronics	12.99	200

1. ORDER BY — Ascending Is the Default

An ORDER BY clause answers "show me this data, but in order". You add it after FROM (and after WHERE, if you have one), then name the column to sort by. By default it sorts in ascending order — smallest number first, A before Z, oldest date first.

🛒 Real-world analogy

ORDER BY is the "Sort by" dropdown on every shopping site. "Price: Low to High" is ORDER BY price; "Price: High to Low" is ORDER BY price DESC; "Name: A–Z" is ORDER BY product_name.

Important: without ORDER BY, SQL makes no promise about row order. The rows might come back in insertion order today and a different order tomorrow. If order matters, you must say so with ORDER BY.

ORDER BY price — cheapest first

Ascending order is the default; ASC is optional.

Try it Yourself »

SQL

-- Sort the products from cheapest to most expensive.
-- ORDER BY sorts the result; ascending (smallest first) is the DEFAULT.
SELECT product_name, price
FROM products
ORDER BY price;

-- These two queries are IDENTICAL — ASC is the default,
-- so you only ever need to type it for clarity:
SELECT product_name, price
FROM products
ORDER BY price ASC;

Result — 6 rows, cheapest → dearest:

product_name	price
Notebook	3.25
Coffee Mug	9.5
USB-C Cable	12.99
Wireless Mouse	24.99
Desk Lamp	32
Mechanical Keyboard	79

2. DESC — Flip to Descending

Add the keyword DESC after the column to reverse the sort: largest number first, or Z before A for text. (There's an ASC keyword too, but since ascending is already the default you rarely need to type it.)

ORDER BY price DESC — dearest first

DESC reverses the order for numbers and text alike.

Try it Yourself »

SQL

-- Add DESC to flip the order: most expensive first.
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- DESC works on text too — Z to A:
SELECT product_name
FROM products
ORDER BY product_name DESC;   -- USB-C Cable, Notebook, Mechanical Keyboard, ...

Result — 6 rows, dearest → cheapest:

product_name	price
Mechanical Keyboard	79
Desk Lamp	32
Wireless Mouse	24.99
USB-C Cable	12.99
Coffee Mug	9.5
Notebook	3.25

Your Turn: sort cheapest first

Fill in the one blank to list every product from cheapest to most expensive. The expected result is in the comments so you can check yourself.

🎯 Your Turn: ORDER BY price

Replace the ___ with the column to sort by (ascending).

Try it Yourself »

SQL

-- 🎯 YOUR TURN — fill in the blank, then press "Try it Yourself"
-- Goal: list every product's name and price, CHEAPEST first.

SELECT product_name, price
FROM products
ORDER BY ___;        -- 👉 the column to sort by (ascending is the default)

-- ✅ Expected result (6 rows, cheapest → dearest):
--    Notebook | 3.25
--    Coffee Mug | 9.50
--    USB-C Cable | 12.99
--    Wireless Mouse | 24.99
--    Desk Lamp | 32.00
--    Mechanical Keyboard | 79.00

3. Multiple Columns — Tie-Breakers

List more than one column, separated by commas, to build a sort hierarchy. SQL sorts by the first column; the second column is only consulted when two rows tie on the first. Each column gets its own direction.

Pro tip: ORDER BY category ASC, price DESC reads as "group alphabetically by category, and inside each group show the most expensive product first". Order the columns from the most important sort key to the least.

ORDER BY category, then price

The second column breaks ties on the first.

Try it Yourself »

SQL

-- Sort by category first, then by price WITHIN each category.
-- The second column is a "tie-breaker": it only matters
-- when two rows share the same category.
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

-- Read it as: group alphabetically by category,
-- and inside each group put the dearest product first.

Result — grouped by category (A→Z), dearest first inside each:

product_name	category	price
Mechanical Keyboard	Electronics	79
Wireless Mouse	Electronics	24.99
USB-C Cable	Electronics	12.99
Desk Lamp	Home	32
Coffee Mug	Kitchen	9.5
Notebook	Stationery	3.25

4. Sort by an Alias or an Expression

You're not limited to sorting by stored columns. You can sort by a calculated column — and the tidy way to do it is to give the calculation an alias with AS, then sort by that alias. The underlying table is untouched; the new column lives only in this result.

ORDER BY a calculated alias

Sort by sale_price (price * 0.9).

Try it Yourself »

SQL

-- You can sort by a calculated column using its ALIAS.
-- Here we knock 10% off and sort by the discounted price.
SELECT
    product_name,
    price,
    price * 0.9 AS sale_price   -- 10% off
FROM products
ORDER BY sale_price ASC;        -- cheapest sale price first

-- Sorting by the raw expression (ORDER BY price * 0.9) works too,
-- but the alias is shorter and easier to read.

Result — cheapest sale price first:

product_name	price	sale_price
Notebook	3.25	2.925
Coffee Mug	9.5	8.55
USB-C Cable	12.99	11.691
Wireless Mouse	24.99	22.491
Desk Lamp	32	28.8
Mechanical Keyboard	79	71.1

5. A Word on NULL Values

A NULL is a missing or unknown value — not zero, not an empty string, just "no value here". When you sort a column that contains NULLs, they get bunched together at one end, but which end depends on the database (SQLite and MySQL put them first in ascending order; PostgreSQL puts them last).

Want control? PostgreSQL lets you write ORDER BY col ASC NULLS LAST. Everywhere else, wrap the column in COALESCE(col, 0) to swap NULLs for a real value before sorting. Our products table has no NULLs, so the example below sorts normally — just keep the rule in mind for tables that do.

NULL handling in sorts

How missing values behave, and how to force them last.

Try it Yourself »

SQL

-- A quick note on NULL (a missing / unknown value).
-- Imagine a discount_percent column where some rows are NULL.
-- In ascending order, most databases group the NULLs together
-- at one end (the start in SQLite/MySQL, the end in PostgreSQL).
SELECT product_name, price
FROM products
ORDER BY price ASC;

-- If you need to force missing values last, swap them for a number:
SELECT product_name, price
FROM products
ORDER BY COALESCE(price, 0) ASC;   -- treat any NULL price as 0

6. Top-N — ORDER BY + LIMIT

This is the combo you'll reach for constantly. Sort the rows the way you want, then add LIMIT n to keep only the first n. ORDER BY ... DESC LIMIT 3 gives you the "top 3"; switch to ASC and it's the "bottom 3" instead.

"3 most expensive products" = ORDER BY price DESC LIMIT 3. "3 best-stocked products" = ORDER BY stock DESC LIMIT 3. The LIMIT always comes after the ORDER BY — sort first, then trim.

Top-N with ORDER BY + LIMIT

The 3 most expensive and the 3 best-stocked products.

Try it Yourself »

SQL

-- The "top-N" pattern: ORDER BY decides the winners,
-- LIMIT keeps only how many you want.
-- The 3 MOST EXPENSIVE products:
SELECT product_name, price
FROM products
ORDER BY price DESC   -- dearest first...
LIMIT 3;              -- ...then keep just the top 3

-- Swap DESC for ASC to get the 3 cheapest.
-- The 3 BEST-STOCKED products:
SELECT product_name, stock
FROM products
ORDER BY stock DESC
LIMIT 3;

Result — 3 most expensive (price DESC, LIMIT 3):

product_name	price
Mechanical Keyboard	79
Desk Lamp	32
Wireless Mouse	24.99

Your Turn: the 3 most expensive

Two blanks this time — pick the direction keyword and the row count to return the three priciest products.

🎯 Your Turn: ORDER BY price DESC LIMIT 3

Fill in the direction and the LIMIT to get the top 3.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — fill in the two blanks.
-- Goal: the 3 MOST EXPENSIVE products (name + price), dearest first.

SELECT product_name, price
FROM products
ORDER BY price ___   -- 👉 the keyword for "highest first"
LIMIT ___;           -- 👉 how many rows to keep

-- ✅ Expected result (exactly 3 rows):
--    Mechanical Keyboard | 79.00
--    Desk Lamp | 32.00
--    Wireless Mouse | 24.99

7. Where ORDER BY Fits in a Query

SQL clauses must appear in a fixed order. ORDER BY always comes after WHERE and before LIMIT:

SELECT columns
FROM table
WHERE conditions      -- 1. filter rows first
ORDER BY columns      -- 2. then sort what's left
LIMIT n;              -- 3. then keep the first n

Mentally, the database filters with WHERE, sorts the survivors with ORDER BY, and only then trims with LIMIT — which is exactly why "top 3 cheapest" works: the cheapest can't be chosen until everything is sorted.

Common Errors (and the fix)

Forgetting DESC: ORDER BY price sorts cheapest-first because ascending is the default. If you wanted the most expensive at the top, you need ORDER BY price DESC.
Sorting by a column you didn't select: SELECT product_name FROM products ORDER BY price is allowed and works — but the result has no price column, so the order can look random to a reader. Select the column you sort by when clarity matters.
Text vs. numbers: sorting a number stored as text sorts it alphabetically, so "100" comes before "9". Make sure a price column is a numeric type, not text.
Expecting order without ORDER BY: a plain SELECT * FROM products may look sorted by id, but SQL guarantees nothing. Never rely on "natural" order — add an explicit ORDER BY.
Putting ORDER BY before WHERE: the clauses have a strict order. It's always WHERE → ORDER BY → LIMIT.

📘 Quick Reference

Syntax	Result
ORDER BY col	Sort ascending (the default)
ORDER BY col ASC	Sort ascending (explicit)
ORDER BY col DESC	Sort descending (highest / Z→A)
ORDER BY a, b DESC	a ascending, b breaks ties descending
ORDER BY alias	Sort by a calculated column's name
ORDER BY col DESC LIMIT n	The top n rows (top-N)
COALESCE(col, 0)	Treat NULLs as a value when sorting

Frequently Asked Questions

Q: Do I have to write ASC?

No. Ascending is the default, so ORDER BY price and ORDER BY price ASC are identical. Type ASC only when it makes a multi-column sort easier to read.

Q: Can I sort by a column I didn't put in SELECT?

Yes — ORDER BY can use any column in the table, selected or not. It works, but selecting the sort column usually makes the result clearer to whoever reads it.

Q: How do I get just the "top 3" of something?

Sort the way you want and add LIMIT 3. For the 3 most expensive: ORDER BY price DESC LIMIT 3. For the 3 cheapest, use ASC.

Q: Why are my rows out of order when I didn't use ORDER BY?

Because SQL never guarantees an order without one. Any apparent ordering is luck and can change. Add ORDER BY whenever order matters.

Mini-Challenge: The 3 Cheapest

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

🎯 Mini-Challenge

The 3 cheapest products — name + price, cheapest first.

Try it Yourself »

SQL

-- 🎯 MINI-CHALLENGE
-- Using ONLY what this lesson covered (SELECT, ORDER BY, DESC/ASC, LIMIT):
--   1. Show product_name and price
--   2. Sort so the CHEAPEST product comes first
--   3. Keep only the 3 cheapest products
--
-- ✅ Expected (exactly 3 rows, cheapest first):
--    Notebook | 3.25
--    Coffee Mug | 9.50
--    USB-C Cable | 12.99

-- your query here

🎉 Lesson Complete

✅ ORDER BY col sorts results; ascending is the default
✅ DESC flips to descending (highest / Z→A first)
✅ A second sort column acts as a tie-breaker for the first
✅ You can sort by an alias or a calculated expression
✅ NULLs clump at one end; COALESCE gives you control
✅ ORDER BY ... LIMIT n gives you the top-N of anything
✅ Next: JOIN operations — combining data from multiple tables

ORDER BY & Sorting

What You'll Learn

Our Sample Table: products

1. ORDER BY — Ascending Is the Default

ORDER BY price — cheapest first

2. DESC — Flip to Descending

ORDER BY price DESC — dearest first

Your Turn: sort cheapest first

🎯 Your Turn: ORDER BY price

3. Multiple Columns — Tie-Breakers

ORDER BY category, then price

4. Sort by an Alias or an Expression

ORDER BY a calculated alias

5. A Word on NULL Values

NULL handling in sorts

6. Top-N — ORDER BY + LIMIT

Top-N with ORDER BY + LIMIT

Your Turn: the 3 most expensive

🎯 Your Turn: ORDER BY price DESC LIMIT 3

7. Where ORDER BY Fits in a Query

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: The 3 Cheapest

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `products`