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