ORDER BY and LIMIT

Sort and cap result sets with ORDER BY and LIMIT. Pagination patterns in web APIs build on these clauses—common in Django queryset SQL and raw PHP queries.

Sorting

SELECT id, customer_id, total, ordered_at
FROM orders
ORDER BY ordered_at DESC;

SELECT name, email
FROM customers
ORDER BY name ASC, id ASC;

Practice: Run in SQLite, DB Fiddle, or Postgres. Create the customers/orders sample from the relational-model lesson if needed.

LIMIT and OFFSET

SELECT id, total
FROM orders
ORDER BY total DESC
LIMIT 3;

SELECT id, total
FROM orders
ORDER BY id
LIMIT 10 OFFSET 10;

LIMIT caps rows; OFFSET skips rows for page 2, page 3, etc. Large offsets can be slow—keyset pagination is an advanced alternative.

NULL sort order

SELECT name, email
FROM customers
ORDER BY email NULLS LAST;

NULLS FIRST / NULLS LAST are widely supported (Postgres, SQLite 3.30+). MySQL may need workarounds—see dialect tracks.

Important interview questions and answers

1. Q: ORDER BY required for deterministic LIMIT?
   A: Without ORDER BY, the engine may return arbitrary rows—pagination becomes inconsistent.
2. Q: OFFSET cost?
   A: Skipping many rows still scans them—deep pages get expensive on large tables.

Self-check

1. How do you fetch the top 5 rows by total descending?
2. Why is ORDER BY important before LIMIT in APIs?

Tip: APIs paginating with OFFSET should add a stable ORDER BY key (often primary key).