Part 04: ORDER BY and LIMIT, Sorting and Paging

Part of the "SQL: Zero to Ninja" series.

You build a product page. The newest items should show first, and you want maybe 20 per page, not all 5,000 at once. So you run your query and... the rows come back in some random jumble, and you get every single one. Two problems. Let's fix both with two small keywords.

The idea in one line

ORDER BY puts your rows in the order you want, and LIMIT grabs just the top few.

The metaphor: dealing cards from a sorted deck

Think of a deck of cards. First you sort the deck (that is ORDER BY). Then you deal off the top just the few you need (that is LIMIT). Sort, then deal. That is the whole post.

all rows  -->  [ ORDER BY: sort the deck ]  -->  [ LIMIT: deal the top few ]

Or picture a leaderboard. You sort players by score, highest first, then show only the top 10. Same two steps.

ORDER BY: sorting the deck

By default, SQL gives rows back in no promised order. ORDER BY fixes that.

SELECT name, price
FROM products
ORDER BY price ASC;   -- ASC = ascending = small to big (this is the default)

DESC flips it to big-to-small:

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

ASC is the default, so you can leave it off. But writing it makes your intent clear.

Sorting by more than one column

Sometimes one column has ties. Two users from the same country, say. You can give a tie-breaker by listing a second column.

SELECT name, country, created_at
FROM users
ORDER BY country ASC, created_at DESC;

Read it like sorting a stack of papers: first make piles by country (A to Z), then inside each pile, sort by created_at newest first.

country  created_at
EG       2026-05-20   <-- inside EG, newest first
EG       2026-01-10
US       2026-04-02   <-- then the US pile starts
US       2026-03-15

The first column is the main sort. The second only breaks ties inside the first.

LIMIT: deal just the top few

Once the deck is sorted, take the top N with LIMIT.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;   -- the 3 most expensive products

This is your "top 10", "latest 5", "biggest order" query. Sort the right way, then LIMIT.

OFFSET: pagination (skip, then deal)

A web page rarely shows everything. It shows page 1, page 2, page 3. OFFSET tells SQL how many rows to skip before it starts dealing.

SELECT name, price
FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;   -- skip the first 20, then take the next 20 (that is page 2)

The pattern for any page is simple. With a fixed page size:

OFFSET = (page number - 1) * page size

So with 10 items per page:

page 1  -->  LIMIT 10 OFFSET 0     (skip 0)
page 2  -->  LIMIT 10 OFFSET 10    (skip 10)
page 3  -->  LIMIT 10 OFFSET 20    (skip 20)

Page 3 skips the first 20 rows, then deals the next 10. That is it.

A real case

A product list page: newest first, 20 per page. Here is page 1 and page 2.

-- Page 1
SELECT id, name, price
FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Page 2
SELECT id, name, price
FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

Same sort every time, only the OFFSET changes as the user clicks "next page". This exact shape powers most product lists, feeds, and search results you have ever scrolled.

Gotchas

1. Paging without ORDER BY. This is the big one. Without ORDER BY, the row order is not guaranteed. The database can hand them back differently each time. So page 1 and page 2 might overlap, or skip rows, and users see the same item twice or miss one. Rule: if you use LIMIT for paging, you must have an ORDER BY, and it must sort on something stable.

   -- WRONG: no ORDER BY, paging can repeat or skip rows
   SELECT id, name FROM products LIMIT 20 OFFSET 20;

   -- RIGHT: stable sort, so pages line up
   SELECT id, name FROM products ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20;

Adding id as a tie-breaker keeps the order stable even when two rows share the same created_at.

1. A huge OFFSET gets slow. OFFSET 100000 makes the database walk past 100,000 rows just to throw them away, every single time. On big tables this drags. The grown-up fix is "keyset" or "cursor" pagination (remember the last id you saw and ask for rows after it). That is a topic for later, just know OFFSET is fine for small pages and gets sluggish deep into huge tables.

2. Forgetting DESC for "newest". Newest first means created_at DESC. ASC would show the oldest first, which is rarely what a feed wants.

Recap

• ORDER BY sorts your rows. ASC is small-to-big (default), DESC is big-to-small.
• List several columns to sort, then tie-break inside that sort.
• LIMIT deals just the top N rows after sorting.
• OFFSET skips rows for pagination: OFFSET = (page - 1) * page size.
• Always pair paging with a stable ORDER BY, or pages can repeat or skip rows.

Your turn

Write the query for page 3 of a user list, 25 users per page, sorted by name A to Z. What is your OFFSET? If you can explain why leaving out ORDER BY would break paging, you have got it.

Next up: Part 05: Aggregates and GROUP BY, where we count, sum, and group rows into useful totals.

Comments

[Mohamed Idris]

Part 04 Practice: ORDER BY and LIMIT

Sort the deck, then deal the top few. Use the shared schema (users, orders, products, order_items). Try each before checking the solution.

1. Cheapest first

List all products by price, cheapest first.

Solution

SELECT name, price
FROM products
ORDER BY price ASC;

Why: ASC sorts small to big. It is the default, but writing it keeps your intent clear.

2. The three biggest orders

Show the id and total of the 3 orders with the highest total.

Solution

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

Why: sort big-to-small with DESC, then LIMIT 3 deals just the top three.

3. Sort by two columns

List users sorted by country A to Z, and inside each country, newest first.

Solution

SELECT name, country, created_at
FROM users
ORDER BY country ASC, created_at DESC;

Why: the first column is the main sort, the second one only breaks ties inside each country.

4. Newest products, page 1

Show the first page of products, newest first, 20 per page.

Solution

SELECT id, name, price
FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

Why: newest first is created_at DESC. Page 1 skips nothing, so OFFSET 0.

5. Newest products, page 3

Same product list, 20 per page, but page 3. What is the OFFSET?

Solution

SELECT id, name, price
FROM products
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

Why: OFFSET = (page - 1) * page size = (3 - 1) * 20 = 40. The extra id DESC keeps the sort stable so pages do not repeat rows.

6. Stable paging challenge

Page 2 of a user list, 25 per page, sorted by name A to Z, with a stable tie-breaker.

Solution

SELECT id, name
FROM users
ORDER BY name ASC, id ASC
LIMIT 25 OFFSET 25;

Why: (2 - 1) * 25 = 25, so skip 25. Adding id ASC breaks ties when two users share a name, keeping page boundaries stable.

You can now sort and page like a pro. Next up is counting and grouping in Part 05. Keep dealing those cards.