📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 3 of 12

Without ORDER BY, a database returns rows in whatever order is convenient for it, which can change from one run to the next. If order matters, say so.

Sort ascending and descending

SELECT name, price FROM products ORDER BY price;          -- cheapest first (ASC is the default)
SELECT name, price FROM products ORDER BY price DESC;     -- most expensive first

Several columns

SELECT region, name FROM customers ORDER BY region, name;
SELECT category, name, price FROM products ORDER BY category, price DESC;

Sorted by the first column; ties broken by the second. Each column has its own ASC/DESC.

Top-N with LIMIT

SELECT name, price FROM products ORDER BY price LIMIT 3;                    -- 3 cheapest
SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 5;   -- 5 latest

LIMIT without ORDER BY gives “any 5 rows”, not the first or latest. Pair them.

Sort by a calculation or alias

SELECT name, price * (1 + gst_rate) AS with_gst
FROM products
ORDER BY with_gst DESC;

Paging: OFFSET

SELECT order_id FROM orders ORDER BY order_id LIMIT 10 OFFSET 20;   -- rows 21-30

NULLs in sorting

SQLite and SQL Server put NULLs first in ascending order; PostgreSQL and Oracle put them last. Add NULLS LAST (PostgreSQL, Oracle, recent SQLite) when it matters.

Order of clauses

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

Always in this order. Lessons 5 adds GROUP BY and HAVING between WHERE and ORDER BY.

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.