ORDER BY & Sorting

Reviewed & published by Brayan K

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.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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

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.

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.

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.

-- The table this lesson uses. Run this block first, then every query
-- below sorts these same six rows.
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT    NOT NULL,
    category     TEXT    NOT NULL,
    price        REAL    NOT NULL,
    stock        INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
  (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
  (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
  (4, 'Notebook',            'Stationery',   3.25, 500),
  (5, 'Desk Lamp',           'Home',        32.00,  80),
  (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- 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;

-- ✅ Expected result:
-- 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.)

-- Uses the products table from the first example.
-- 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;   -- Wireless Mouse, USB-C Cable, Notebook, ...

-- ✅ Expected output:
-- product_name
-- Wireless Mouse
-- USB-C Cable
-- Notebook
-- Mechanical Keyboard
-- Desk Lamp
-- Coffee Mug

-- ✅ Expected result:
-- product_name
-- Wireless Mouse
-- USB-C Cable
-- Notebook
-- Mechanical Keyboard
-- Desk Lamp
-- Coffee Mug

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.

-- Uses the products table from the first example.
-- 🎯 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.

-- Uses the products table from the first example.
-- 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.

-- ✅ Expected result:
-- 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.

-- Uses the products table from the first example.
-- 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.
-- (The long tails on some sale prices are ordinary floating-point maths.)

-- ✅ Expected result:
-- product_name | price | sale_price
-- Notebook | 3.25 | 2.9250000000000003
-- 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.10000000000001

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).

-- Uses the products table from the first example.
-- 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

-- ✅ Expected result:
-- 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

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.

-- Uses the products table from the first example.
-- 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;

-- The output below belongs to that LAST query, the 3 best-stocked.

-- ✅ Expected result:
-- product_name | stock
-- Notebook | 500
-- Coffee Mug | 300
-- USB-C Cable | 200

Your Turn: the 3 most expensive

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

-- Uses the products table from the first example.
-- 🎯 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.

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.

-- Uses the products table from the first example.
-- 🎯 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

Practice quiz

Continue this course

• Previous: WHERE Clause & Filtering
• Next: SELECT DISTINCT — Remove duplicate rows from your results
• Quick reference: SQL cheat sheet › Querying Data

Related lessons

• SELECT