SQL ORDER BY & LIMIT — Sorting and Pagination

Processing Pipeline

WHERE → ORDER BY → LIMIT

Sample Data

ordersjson

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

usersjson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

ORDER BY

Highest order amounts firstjson

1SELECT order_id, total_amount
2FROM orders
3ORDER BY total_amount DESC;

Expected outputjson

1| order_id | total_amount |
2|---|---|
3| 1001 | 49.99 |
4| 1002 | 12.50 |
5| 1003 | 8.00 |

Multi-column sortjson

1SELECT email
2FROM users
3ORDER BY is_active DESC, email ASC;

LIMIT — Top N

Top 2 orders by amountjson

1SELECT order_id, total_amount
2FROM orders
3ORDER BY total_amount DESC
4LIMIT 2;

OFFSET — Pagination

Page 2 with 10 rows per page (rows 11–20)json

1SELECT order_id, total_amount
2FROM orders
3ORDER BY order_id ASC
4LIMIT 10 OFFSET 10;

Dialect differences

PostgreSQL, MySQL, and SQLite use LIMIT/OFFSET. SQL Server uses OFFSET n ROWS FETCH NEXT m ROWS ONLY — see the SQL Server to MySQL converter for rewrites.

Stable Sorting

Approach	Risk	Recommendation
ORDER BY amount DESC	Ties may shuffle between pages	Add order_id as second sort key
ORDER BY amount DESC, order_id ASC	Stable order	Preferred for pagination

Practice Prompts

Return the single largest order.
List users alphabetically by email.
Write a query for page 1 (first 2 orders) sorted by order_id.

Try These Tools

SQL Formatter

Format ORDER BY clauses clearly

Open tool

SQL Compare

Compare dialect pagination syntax

Open tool

Continue Learning

WHERE — Filtering Rows

Filter before sorting

SQL COUNT() Function

Count rows before grouping

Frequently Asked Questions

Does ORDER BY run before or after WHERE?

Logically, WHERE filters first, then ORDER BY sorts the remaining rows, then LIMIT takes the first N.

What is the default sort order?

ASC (ascending) is default. Use DESC for descending (highest/newest first).

How do I paginate in SQL?

Use LIMIT page_size OFFSET (page - 1) * page_size on PostgreSQL/MySQL/SQLite. SQL Server uses OFFSET/FETCH syntax.

Why add a tie-breaker column to ORDER BY?

If two rows tie on the first sort column, order is undefined. Add a unique column (e.g. id) for stable pagination.

Is large OFFSET slow?

Yes on big tables — the engine must skip many rows. Keyset pagination is an advanced alternative.