A table without ORDER BY is an unordered set. The engine returns rows in whatever order is cheapest at this moment — could be insertion order, could be index order, could change after a vacuum or an upgrade. If row order matters in your output, you must say so explicitly.

Basic syntax — one column, ascending or descending

ORDER BY is the last clause to run, after SELECT projects the result. The default direction is ASC (ascending) — you don't have to write it, but doing so makes intent obvious.

-- Cheapest products first (ASC is the default; explicit is clearer)
SELECT name, price
FROM   products
ORDER BY price ASC;

-- Most expensive first
SELECT name, price
FROM   products
ORDER BY price DESC;

name           | price
---------------+------
Database Mug   |  9.50
SQL Cookbook   | 34.99
Designing Data | 42.00

Multi-column sort — primary, tie-breaker, tie-breaker…

You can list more than one sort key. The first key is the primary sort; subsequent keys break ties only when the previous key is equal. Each key gets its own direction.

-- "books" first (alphabetical), then within each category, highest price first
SELECT name, category, price
FROM   products
ORDER BY category ASC, price DESC;

The mental model: imagine bucketing rows by the first key, then sorting each bucket by the second key, then concatenating the buckets. Add a third key and you sort within each (category, price) pair, and so on.

Sorting by position or alias

ORDER BY can refer to columns three ways: by name, by 1-based position in the SELECT list, or by an alias defined in the SELECT list. Aliases work here because ORDER BY runs after SELECT in the logical processing order (Chapter 01, §2).

-- By name
SELECT name, price FROM products ORDER BY price DESC;

-- By position — "2" means the second column in the SELECT list
SELECT name, price FROM products ORDER BY 2 DESC;

-- By alias defined in SELECT
SELECT name, price * 1.1 AS price_with_tax
FROM   products
ORDER BY price_with_tax DESC;

Where do NULLs go?

A sort key that contains NULL raises a question the SQL standard does not answer: should NULLs sort first or last? Every dialect picks a default — and the defaults disagree. This is one of the few places where the same query gives a different row order on different engines.

Forcing NULL placement (portable enough)

PostgreSQL and SQLite (3.30+) accept the standard NULLS FIRST / NULLS LAST qualifier. MySQL does not — there you sort on a synthetic key that pushes NULLs to whichever end you want.

-- ✅ PostgreSQL, SQLite 3.30+
SELECT full_name, created_at
FROM   users
ORDER BY created_at ASC NULLS LAST;

-- ✅ MySQL workaround — sort by "is NULL" first
SELECT full_name, created_at
FROM   users
ORDER BY (created_at IS NULL) ASC,   -- 0 (not null) before 1 (null)
         created_at ASC;
-- "(created_at IS NULL)" returns 0 or 1; 0 sorts first → NULLs end up LAST.

LIMIT n says "give me at most n rows." OFFSET k says "skip the first k of them." Together they're the textbook recipe for "page 2 of search results" — and they work fine until your data grows. Then the cost of OFFSET starts hurting in a way that's worth understanding before you ship it.

LIMIT basics

LIMIT almost always appears with ORDER BY — "top 5" only makes sense if you've decided what "top" means. Without an ORDER BY, LIMIT just hands back some n rows, with no guarantee about which n.

-- Three cheapest products
SELECT name, price
FROM   products
ORDER BY price ASC
LIMIT  3;

-- Most recent paid order
SELECT id, user_id, placed_at
FROM   orders
WHERE  status = 'paid'
ORDER BY placed_at DESC
LIMIT  1;

OFFSET for "page N"

OFFSET k LIMIT n means "discard the first k rows of the ordered result, then return the next n." For a 20-per-page list, page 1 is OFFSET 0 LIMIT 20, page 2 is OFFSET 20 LIMIT 20, page 47 is OFFSET 920 LIMIT 20.

-- Page 2 of products, 2 per page, cheapest first
SELECT name, price
FROM   products
ORDER BY price ASC
LIMIT  2 OFFSET 2;

The OFFSET performance cliff

Here's the part most tutorials skip. OFFSET 1000 doesn't magically jump to row 1001. The database has to produce the first 1000 rows in sort order, then throw them away. Doubling the page number roughly doubles the work. By page 5000 the query is doing 100,000× the work of page 1 — and the user only ever sees 20 rows of output.

Keyset pagination — paginate by a unique anchor

Instead of "skip the first 19,980 rows", keyset pagination says "give me the next 20 rows after this specific sort-key value." It needs the same ordering, but the database can use an index to jump directly to the cursor — constant time, regardless of how deep the user has paged.

-- First page: most recent orders (no cursor yet)
SELECT id, placed_at
FROM   orders
ORDER BY placed_at DESC, id DESC   -- secondary key breaks ties
LIMIT  20;

-- Subsequent pages — pass back the last row's (placed_at, id) from the previous page.
-- Suppose the last row of page 1 was placed_at = '2025-02-11 11:30:00', id = 1002.
SELECT id, placed_at
FROM   orders
WHERE  (placed_at, id) < ('2025-02-11 11:30:00', 1002)   -- row-tuple comparison
ORDER BY placed_at DESC, id DESC
LIMIT  20;

Two important details:

• Tie-breaking column. If two rows have the same placed_at, comparing on that alone could skip rows or repeat them. Including id guarantees a total order.
• Row-tuple comparison. (a, b) < (x, y) means "a < x, or (a = x and b < y)" — exactly the lexicographic test you'd write by hand. PostgreSQL, MySQL 8+, and SQLite support it. On older engines, expand it manually.

The list of expressions between SELECT and FROM is called the projection. It decides which columns are in the result, what they're named, and whether duplicates are kept. Three habits separate exploratory queries from production-ready ones.

Specific columns vs SELECT *

In an interactive shell, SELECT * is a quick way to see what a table looks like. In application code it's a footgun for three reasons:

• Bandwidth. A row with 30 columns might be 4 KB even when your code only reads 2. Multiply by every query, every user, every day.
• Schema drift. Tomorrow someone adds a password_hash column to users. Your "innocent" SELECT * is now leaking secrets into your API response.
• Order assumptions. SELECT * hands back columns in whatever order the table defines them. Code that destructures by position breaks when a column is added or reordered.

-- ⚠ Exploratory only
SELECT * FROM users;

-- ✅ Production: spell it out
SELECT id, email, full_name FROM users;

Column aliases with AS

An alias renames a column in the result set. The underlying table is untouched. Aliases serve three purposes: hide ugly expressions, give computed columns meaningful names, and disambiguate when two joined tables have a column called id (Chapter 05).

-- Computed column with a clear name
SELECT id,
       name,
       price * 1.1 AS price_with_tax,
       price * 0.9 AS sale_price
FROM   products;

-- The AS keyword is optional — these two are identical:
SELECT name AS product_name FROM products;
SELECT name    product_name FROM products;   -- legal but harder to read

-- Aliases with spaces or special chars need double quotes
SELECT name AS "Product Name", price AS "Price (USD)"
FROM   products;

DISTINCT — remove duplicate rows

DISTINCT applies to the whole row the SELECT produces — not just the first column. This is the part beginners regularly get wrong.

-- 4 rows — unique orders that have at least one line item
SELECT DISTINCT order_id FROM order_items;

-- 6 rows — every (order, product) combination is unique already, so DISTINCT does nothing useful
SELECT DISTINCT order_id, product_id FROM order_items;

-- ⚠ A common confusion: this counts unique categories, NOT unique products in each category
SELECT DISTINCT category FROM products;        -- 2 rows: 'books', 'merch'

-- ⚠ This counts unique (category, name) pairs — basically every product, because names are already unique
SELECT DISTINCT category, name FROM products;  -- one row per product

Putting the full clause order to use

A query that pulls the three cheapest non-cancelled orders, with computed totals and a friendly column name, exercises everything in this chapter and Chapter 02:

SELECT id            AS order_id,
       status,
       placed_at,
       (SELECT SUM(quantity * unit_price)
        FROM   order_items oi
        WHERE  oi.order_id = orders.id) AS total_usd
FROM   orders
WHERE  status != 'cancelled'                -- §2 — filter rows
ORDER BY placed_at DESC NULLS LAST,         -- §3.1 — sort, with explicit NULL placement
         id DESC                            -- tie-breaker
LIMIT  3;                                   -- §3.2 — only the first three