Sorting and Paging: ORDER BY, LIMIT and OFFSET

A table is a set of rows, and a set has no order. If you do not ask for an order, the database hands rows back in whatever order is cheapest for it today, and that order can change after an update, a new index or a version upgrade. ORDER BY is how you ask for an order, and LIMIT is how you ask for only the first few rows of it.

You will use the two together constantly: "the 10 newest support tickets", "the 20 most expensive LLM calls this week", "page 3 of the product list", "the 5 nearest documents" in a vector search. A query that exports training data in a fixed, repeatable order is also part of what makes an ML experiment reproducible.

What you will learn

• Sort with ORDER BY, ascending and descending, by several columns, and by an expression or alias.
• Add a tie-breaker so the order is fully fixed.
• Where NULL values land in SQLite, MySQL and PostgreSQL, and how to move them.
• Take the top N rows with LIMIT, and the other spellings: FETCH FIRST, TOP.
• Page through results with OFFSET, and why big offsets are slow and can skip or repeat rows.

No ORDER BY, no promised order

Run SELECT name FROM products; in SQLite and the rows will probably come back in product_id order. That is an accident of how SQLite stores this small table, not a promise. PostgreSQL, for example, often returns recently updated rows last. The rule is simple: if the order matters, write ORDER BY. Every multi-row query in this tutorial does.

ORDER BY: ascending and descending

ORDER BY column sorts from smallest to largest. That is ASC (ascending), the default, so you rarely write it. DESC (descending) sorts from largest to smallest:

SELECT name, price
FROM products
ORDER BY price DESC;

+-----------------------------+-------+
| name                        | price |
+-----------------------------+-------+
| 27-inch Monitor             | 18500 |
| AI Engineering Bootcamp     | 15000 |
| Noise-Cancelling Headphones |  7500 |
| Mechanical Keyboard         |  4500 |
| SQL Masterclass             |  3000 |
| Hands-On Machine Learning   |  2500 |
| USB-C Hub                   |  2200 |
| Laptop Stand                |  1500 |
| Python Crash Course         |  1200 |
| Gift Card                   |  1000 |
| Wireless Mouse              |   900 |
+-----------------------------+-------+

"Smallest" depends on the type: numbers sort by value, text sorts character by character (so '27-inch Monitor' comes before 'AI Engineering Bootcamp', because digits come before letters), and ISO dates like '2026-03-08' sort in calendar order. ORDER BY can also use a column you did not select.

SELECT name
FROM customers
ORDER BY joined_on DESC     -- newest customer first; joined_on is not shown
LIMIT 3;

+---------------+
| name          |
+---------------+
| Karim Uddin   |
| Mitu Das      |
| Imran Hossain |
+---------------+

Several columns, and tie-breakers

With several columns, the database sorts by the first one, and uses the second only to order rows that tie on the first, and so on. Each column gets its own direction:

SELECT department, name, salary
FROM employees
ORDER BY department, salary DESC;

+-------------+-----------------+--------+
| department  | name            | salary |
+-------------+-----------------+--------+
| Data        | Lamia Karim     | 160000 |
| Data        | Priya Sen       | 110000 |
| Data        | Shuvo Roy       |  95000 |
| Engineering | Hasan Mahmud    | 180000 |
| Engineering | Nusrat Jahan    | 120000 |
| Engineering | Zahid Hasan     | 120000 |
| Management  | Ayesha Siddiqua | 250000 |
+-------------+-----------------+--------+

Departments are A to Z; inside each department, the highest salary comes first. Now look at Nusrat and Zahid: both earn 120000. Nothing in this query says which of them comes first, so the database may return them either way, and a different database may choose differently. Add a tie-breaker, a last column that is unique, such as the primary key:

SELECT department, name, salary
FROM employees
ORDER BY department, salary DESC, employee_id;

+-------------+-----------------+--------+
| department  | name            | salary |
+-------------+-----------------+--------+
| Data        | Lamia Karim     | 160000 |
| Data        | Priya Sen       | 110000 |
| Data        | Shuvo Roy       |  95000 |
| Engineering | Hasan Mahmud    | 180000 |
| Engineering | Nusrat Jahan    | 120000 |
| Engineering | Zahid Hasan     | 120000 |
| Management  | Ayesha Siddiqua | 250000 |
+-------------+-----------------+--------+

Now the order is fully decided. This matters more than it looks: unstable order is how a test passes on your laptop and fails in CI, how page 2 of a list repeats a row from page 1, and how two exports of "the same" training set end up different.

ASC and DESC apply to one column. ORDER BY department, salary DESC means department ascending, salary descending. It does not reverse both.

Sorting by an expression or an alias

You can sort by a calculation. Here is each line of an order with its total (quantity × unit price), biggest lines first:

SELECT order_id, product_id, quantity * unit_price AS line_total
FROM order_items
ORDER BY line_total DESC, order_id, product_id
LIMIT 5;

+----------+------------+------------+
| order_id | product_id | line_total |
+----------+------------+------------+
|        5 |          5 |      18500 |
|       10 |          5 |      18500 |
|        6 |          7 |      15000 |
|       13 |          7 |      15000 |
|        2 |          3 |       4500 |
+----------+------------+------------+

Sorting by the alias line_total works because, in the logical order of a query (see How SQL Works: Statements, Command Types and Query Order), ORDER BY runs after SELECT, so the alias already exists. You could also write ORDER BY quantity * unit_price DESC, or even ORDER BY 3 DESC (the third selected column). Avoid the number form in real code: add a column to the SELECT and the query silently sorts by something else.

Where do NULLs go?

Courses and the gift card have no stock figure (NULL). When you sort by stock, the databases disagree on where those rows go:

SQLite and MySQL treat NULL as smaller than any value; PostgreSQL treats it as larger. Here is the same query on PostgreSQL:

-- PostgreSQL
SELECT name, stock
FROM products
ORDER BY stock, product_id
LIMIT 5;

+-----------------------------+-------+
| name                        | stock |
+-----------------------------+-------+
| USB-C Hub                   |     0 |
| 27-inch Monitor             |     5 |
| Noise-Cancelling Headphones |     8 |
| Mechanical Keyboard         |    12 |
| Hands-On Machine Learning   |    15 |
+-----------------------------+-------+

In SQLite and PostgreSQL you can say exactly what you want with NULLS FIRST or NULLS LAST:

SELECT name, stock
FROM products
ORDER BY stock NULLS LAST, product_id;

+-----------------------------+-------+
| name                        | stock |
+-----------------------------+-------+
| USB-C Hub                   |     0 |
| 27-inch Monitor             |     5 |
| Noise-Cancelling Headphones |     8 |
| Mechanical Keyboard         |    12 |
| Hands-On Machine Learning   |    15 |
| Laptop Stand                |    25 |
| Python Crash Course         |    40 |
| Wireless Mouse              |    60 |
| SQL Masterclass             | NULL  |
| AI Engineering Bootcamp     | NULL  |
| Gift Card                   | NULL  |
+-----------------------------+-------+

MySQL does not accept NULLS LAST. The portable trick is to sort by "is it NULL?" first. stock IS NULL is 0 for real values and 1 for NULLs, so the NULLs go to the end:

-- MySQL
SELECT name, stock
FROM products
ORDER BY stock IS NULL, stock, product_id;

+-----------------------------+-------+
| name                        | stock |
+-----------------------------+-------+
| USB-C Hub                   |     0 |
| 27-inch Monitor             |     5 |
| Noise-Cancelling Headphones |     8 |
| Mechanical Keyboard         |    12 |
| Hands-On Machine Learning   |    15 |
| Laptop Stand                |    25 |
| Python Crash Course         |    40 |
| Wireless Mouse              |    60 |
| SQL Masterclass             | NULL  |
| AI Engineering Bootcamp     | NULL  |
| Gift Card                   | NULL  |
+-----------------------------+-------+

This works in SQLite and PostgreSQL too. When a result feeds a report or a model, decide where missing values belong and say it in the query; do not leave it to the database's default.

LIMIT: just the first N rows

LIMIT n keeps the first n rows of the sorted result. With ORDER BY it answers "top N" questions:

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

+-----------------------------+-------+
| name                        | price |
+-----------------------------+-------+
| 27-inch Monitor             | 18500 |
| AI Engineering Bootcamp     | 15000 |
| Noise-Cancelling Headphones |  7500 |
+-----------------------------+-------+

LIMIT runs last, after sorting. That is why "top 3" works: the database sorts all rows, then cuts. LIMIT without ORDER BY just means "any 3 rows", which is fine for a quick peek at a table and wrong for anything else.

Other spellings of LIMIT

LIMIT is not in the SQL standard, so dialects differ. The standard form is FETCH FIRST n ROWS ONLY:

The standard form runs on PostgreSQL, and it can do something LIMIT cannot. Ask for the four best-paid employees: two people share fourth place.

-- PostgreSQL
SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 4 ROWS WITH TIES;

+-----------------+--------+
| name            | salary |
+-----------------+--------+
| Ayesha Siddiqua | 250000 |
| Hasan Mahmud    | 180000 |
| Lamia Karim     | 160000 |
| Zahid Hasan     | 120000 |
| Nusrat Jahan    | 120000 |
+-----------------+--------+

WITH TIES (PostgreSQL 13+, SQL Server's TOP 4 WITH TIES) keeps every row that ties with the last one, so you get 5 rows. Notice also that PostgreSQL listed Zahid before Nusrat: with no tie-breaker, tied rows come in whatever order the engine finds convenient. LIMIT 4 would quietly drop one of the two, and which one is up to the database. For a leaderboard or a "top customers" bonus, that is a real fairness bug.

Pagination with OFFSET

OFFSET m skips the first m rows. Page p of size k is LIMIT k OFFSET (p − 1) × k. This is how a web page or an API shows "orders, 5 per page". In Python, pass the numbers as parameters:

from sqlhelp import con

def orders_page(page, size=5):
    """One page of orders, newest first."""
    rows = con.execute(
        """SELECT order_id FROM orders
           ORDER BY order_date DESC, order_id DESC
           LIMIT ? OFFSET ?""",
        (size, (page - 1) * size),
    ).fetchall()
    return [r[0] for r in rows]

for page in (1, 2, 3, 4):
    print(f"page {page}:", orders_page(page))

page 1: [14, 13, 12, 11, 10]
page 2: [9, 8, 7, 6, 5]
page 3: [4, 3, 2, 1]
page 4: []

14 orders make two full pages, a page of 4 and an empty page 4. An empty page is how the caller knows to stop. Notice the tie-breaker order_id DESC: without it, two orders on the same day could swap places between requests and appear on two pages, or on none.

Two costs of OFFSET

It gets slower the deeper you go. To serve OFFSET 100000, the database still finds and sorts the first 100,000 rows, then throws them away. Page 1 is fast; page 5,000 is not.

It can skip or repeat rows when data changes. Watch what happens when a new order arrives while someone is between page 1 and page 2:

print("page 1:", orders_page(1))

con.execute("INSERT INTO orders VALUES (15, 4, '2026-06-20', 'pending')")   # a new order arrives

print("page 2:", orders_page(2))

con.execute("DELETE FROM orders WHERE order_id = 15")                        # put the data back
con.commit()

page 1: [14, 13, 12, 11, 10]
page 2: [10, 9, 8, 7, 6]

Order 10 appears on both pages: the new order pushed everything down by one. The fix for both problems is keyset pagination: instead of "skip 5 rows", ask for "rows after the last one I saw", for example WHERE order_id < 10 ORDER BY order_id DESC LIMIT 5. It uses WHERE, which you learn on the next page; the advanced tutorial compares the two methods properly. For small admin screens, OFFSET is perfectly fine.

Where you will use this

• Dashboards and APIs: "latest 20 orders", "page 3 of results". Every list endpoint has an ORDER BY and a LIMIT.
• LLM observability: the 10 slowest or most expensive model calls (ORDER BY latency_ms DESC LIMIT 10) are where you start looking for problems.
• Vector search: in PostgreSQL with pgvector, "find the 5 closest chunks" is literally ORDER BY embedding <=> :query LIMIT 5.
• Exploring data: LIMIT 10 is the SQL version of df.head(). But the first 1,000 rows are not a random sample: they are often the oldest. Never train or evaluate a model on "the first N rows".

Common mistakes

1. LIMIT without ORDER BY. "The top 5" without a sort is any 5 rows. Always pair them: ORDER BY price DESC LIMIT 5.
2. No tie-breaker. Sorting by a column with repeats (dates, salaries, statuses) leaves ties in random order, and pages overlap. End with a unique column: ORDER BY order_date DESC, order_id DESC.
3. Thinking DESC applies to every column. ORDER BY department, salary DESC sorts department ascending. Write the direction after each column that needs it.
4. MySQL's LIMIT a, b backwards. LIMIT 10, 5 means skip 10, take 5, not the other way round. Prefer LIMIT 5 OFFSET 10, which reads clearly and runs in all three databases.
5. Numbers stored as text sort as text. '10' comes before '9' because '1' < '9':
   SQLCopy

   SELECT column1 AS version
   FROM (VALUES ('9'), ('10'), ('100'))
   ORDER BY version;

   OutputCopy

   +---------+
   | version |
   +---------+
   | 10      |
   | 100     |
   | 9       |
   +---------+

   Store numbers in number columns. If you cannot, sort by CAST(version AS INTEGER) (more on CAST in Text, Number and Date Functions, CAST and COALESCE).

Try it yourself

1. Easy: List all payments, largest amount first. Break ties by payment_id.
2. Medium: Show page 2 of the product list sorted by name, 4 products per page (columns name and price).
3. Medium: List products by stock from highest to lowest, with the products that have no stock figure at the very end, in a way that also runs on MySQL.
4. Hard: Find the second most expensive product using LIMIT and OFFSET. Then explain why "the second-best-paid employee" with LIMIT 1 OFFSET 1 on employees is a fair question, but "the fourth-best-paid employee" with LIMIT 1 OFFSET 3 is not.

Answers

-- 1
SELECT payment_id, order_id, amount
FROM payments
ORDER BY amount DESC, payment_id;

-- 2: page 2 skips the first 4
SELECT name, price
FROM products
ORDER BY name, product_id
LIMIT 4 OFFSET 4;

-- 3: "stock IS NULL" is 0 or 1, so NULLs sort last
SELECT name, stock
FROM products
ORDER BY stock IS NULL, stock DESC, product_id;

-- 4
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

For 4: salaries in order are 250000, 180000, 160000, 120000, 120000, … The second row is clearly Hasan Mahmud. The fourth and fifth rows tie, so "the fourth-best-paid employee" could be Nusrat or Zahid; LIMIT 1 OFFSET 3 picks one without telling you. Better questions are "the fourth-highest distinct salary" (SELECT DISTINCT salary … LIMIT 1 OFFSET 3, which gives 120000) or "everyone who earns 120000".

Summary

• Without ORDER BY, row order is not defined. Ask for the order you need.
• ASC is the default; DESC reverses; each column has its own direction. End with a unique tie-breaker.
• You can sort by expressions and by SELECT aliases, because ORDER BY runs after SELECT.
• In ascending order, NULLs sort first in SQLite and MySQL, last in PostgreSQL. Use NULLS LAST or ORDER BY col IS NULL, col to choose.
• LIMIT n OFFSET m gives top-N lists and pages. Large offsets are slow and pages shift when data changes; keyset pagination fixes both.

Next: Filtering Rows: WHERE, AND, OR and NOT. So far every query has looked at all the rows. Next you choose which rows to keep, then sort and limit only those.