Querying Data: SELECT and WHERE · Lesson 6 of 30

SQL ORDER BY and LIMIT: Sorting and Paging

5 min read Updated Nov 7, 2026 Saurabh Samir

On this page

ORDER BY
Sorting by several columns
Sorting by expressions and aliases
Where do NULLs go?
LIMIT: top-N queries
Pagination with LIMIT and OFFSET
Practice
Summary

A table has no guaranteed order. Without ORDER BY, the database may return rows in any order it likes – and that order can change after an update, an index change or a version upgrade. Whenever order matters (leaderboards, latest orders, alphabetical lists, pagination), say so with ORDER BY. LIMIT then takes just the first rows.

In this lesson

Sorting ascending and descending
Sorting by several columns and by expressions
Where NULLs go
Top-N queries and pagination with LIMIT and OFFSET

ORDER BY

SELECT name, price
FROM products
ORDER BY price DESC;        -- ASC (the default) = low to high, DESC = high to low

Output

+---------------------+---------+
| name                | price   |
+---------------------+---------+
| Mechanical Keyboard | 2499.00 |
| Laptop Stand        | 1499.00 |
| USB-C Hub           | 1299.00 |
| Desk Lamp           |  899.00 |
| Wireless Mouse      |  599.00 |
| JavaScript Guide    |  550.00 |
| Clean Code          |  450.00 |
| SQL in 10 Minutes   |  350.00 |
| Gel Pen Pack        |  120.00 |
| A5 Notebook         |   90.00 |
+---------------------+---------+

Sorting by several columns

The second column only decides the order between rows that tie on the first:

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

Output

+--------------+-------------+--------+
| name         | department  | salary |
+--------------+-------------+--------+
| Rohit Mehta  | Engineering | 180000 |
| Dev Patel    | Engineering | 120000 |
| Sana Iqbal   | Engineering | 120000 |
| Priya Sharma | Engineering |  95000 |
| Kavya Reddy  | HR          |  90000 |
| Imran Shaikh | HR          |  65000 |
| Anita Desai  | Management  | 250000 |
| Farhan Ali   | Sales       | 140000 |
| Lata Joshi   | Sales       |  85000 |
| Tom Mathew   | Sales       |  78000 |
+--------------+-------------+--------+

Within Engineering, Sana and Dev both earn ₹1,20,000, so the third key – name – breaks the tie. Adding a unique column as the last sort key makes the order fully deterministic, which matters for pagination.

Sorting by expressions and aliases

SELECT name, price, stock, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC          -- aliases work in ORDER BY
LIMIT 3;

Output

+---------------------+---------+-------+-------------+
| name                | price   | stock | stock_value |
+---------------------+---------+-------+-------------+
| Mechanical Keyboard | 2499.00 |    35 |    87465.00 |
| Wireless Mouse      |  599.00 |   120 |    71880.00 |
| Gel Pen Pack        |  120.00 |   500 |    60000.00 |
+---------------------+---------+-------+-------------+

-- Custom order: pending first, then shipped, then delivered, then cancelled
SELECT order_id, status
FROM orders
WHERE customer_id IN (1, 3, 4)
ORDER BY FIELD(status, 'pending', 'shipped', 'delivered', 'cancelled'), order_id;

Output

+----------+-----------+
| order_id | status    |
+----------+-----------+
|      111 | pending   |
|      109 | shipped   |
|      101 | delivered |
|      103 | delivered |
|      105 | delivered |
|      112 | delivered |
|      104 | cancelled |
+----------+-----------+

FIELD() is MySQL-specific; the portable version is ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'shipped' THEN 2 … END (CASE is covered in chapter 3).

Where do NULLs go?

MySQL treats NULL as smaller than any value: NULLs come first in ascending order and last in descending order. (PostgreSQL and Oracle do the opposite.) To control it explicitly:

SELECT name, city
FROM customers
ORDER BY city IS NULL, city;        -- false (0) before true (1): NULL cities go last

Output

+-------------+-----------+
| name        | city      |
+-------------+-----------+
| Kiran Rao   | Bengaluru |
| Ravi Kumar  | Delhi     |
| Zoya Khan   | Delhi     |
| Meera Nair  | Kochi     |
| Arjun Singh | Mumbai    |
| Asha Verma  | Pune      |
| Vikram Das  | Pune      |
| Neha Gupta  | NULL      |
+-------------+-----------+

LIMIT: top-N queries

-- The 3 highest-paid employees
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Output

+-------------+--------+
| name        | salary |
+-------------+--------+
| Anita Desai | 250000 |
| Rohit Mehta | 180000 |
| Farhan Ali  | 140000 |
+-------------+--------+

Common mistake: LIMIT without ORDER BY

SELECT … LIMIT 3 without ORDER BY returns some three rows, not the "first" three. Always pair LIMIT with ORDER BY. Also note ties: if the 3rd and 4th salaries were equal, LIMIT 3 would silently drop one – window functions (chapter 7) handle "top 3 including ties" properly.

Database	Top 3 syntax
MySQL, PostgreSQL, SQLite	`… ORDER BY salary DESC LIMIT 3`
SQL Server	`SELECT TOP 3 … ORDER BY salary DESC`
Standard SQL (Oracle 12c+, PostgreSQL)	`… ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY`

Pagination with LIMIT and OFFSET

To show results in pages of n rows, skip (page − 1) × n rows:

-- Page 2 of the product list, 4 products per page
SELECT product_id, name, price
FROM products
ORDER BY product_id
LIMIT 4 OFFSET 4;          -- MySQL also accepts LIMIT 4, 4  (offset, count)

Output

+------------+-------------------+---------+
| product_id | name              | price   |
+------------+-------------------+---------+
|          5 | SQL in 10 Minutes |  350.00 |
|          6 | Gel Pen Pack      |  120.00 |
|          7 | A5 Notebook       |   90.00 |
|          8 | Laptop Stand      | 1499.00 |
+------------+-------------------+---------+

Pages of 4 products

Page 1LIMIT 4 OFFSET 0 → products 1–4
Page 2LIMIT 4 OFFSET 4 → products 5–8
Page 3LIMIT 4 OFFSET 8 → products 9–10

Large tables: keyset pagination

A big OFFSET is slow – to show page 10,000 the database still reads and throws away all the earlier rows. For feeds and infinite scroll, remember the last value you showed and continue from it: WHERE product_id > 8 ORDER BY product_id LIMIT 4. With an index this is fast on any page.

Practice

Try it yourself

Show the two most recent orders that are not cancelled.

Show solution

SELECT order_id, order_date, status
FROM orders
WHERE status <> 'cancelled'
ORDER BY order_date DESC
LIMIT 2;

Output

+----------+------------+-----------+
| order_id | order_date | status    |
+----------+------------+-----------+
|      112 | 2025-11-06 | delivered |
|      111 | 2025-11-05 | pending   |
+----------+------------+-----------+

Show the three customers who signed up most recently.

Show solution

SELECT name, signup_date
FROM customers
ORDER BY signup_date DESC
LIMIT 3;

Output

+-------------+-------------+
| name        | signup_date |
+-------------+-------------+
| Vikram Das  | 2025-07-30  |
| Neha Gupta  | 2025-06-25  |
| Arjun Singh | 2025-05-18  |
+-------------+-------------+

Quick check

What is the default sort direction?

Ascending (ASC).

Which OFFSET gives page 5 with 20 rows per page?

OFFSET 80, i.e. LIMIT 20 OFFSET 80.

Is the row order guaranteed without ORDER BY?

No – never rely on it.

Summary

ORDER BY sorts by one or more columns or expressions, ASC or DESC; add a unique tie-breaker.
MySQL sorts NULLs first in ascending order; use col IS NULL to move them.
LIMIT takes the first rows (always with ORDER BY); LIMIT/OFFSET pages; keyset pagination scales.

Finished this lesson?

Mark it complete to track your progress, then carry on with the next lesson.

Next lesson