SQL ORDER BY and LIMIT: Sorting and Pagination Made Easy

You've written the perfect query, but the results are all over the place. Customer names are jumbled, dates are random, and finding the highest sale requires scrolling through thousands of rows. Sound familiar?

This is where ORDER BY and LIMIT come to the rescue. These two clauses transform chaotic query results into organized, manageable data. Let's explore how to use them effectively — and avoid the performance traps that catch many developers off guard.

Understanding ORDER BY

ORDER BY sorts your query results based on one or more columns. By default, it sorts in ascending order (A to Z, smallest to largest, earliest to latest).

-- Sort by name (A to Z)
SELECT * FROM customers
ORDER BY name;

-- Sort by name (Z to A)
SELECT * FROM customers
ORDER BY name DESC;

Ascending vs Descending

• ASC (default): Ascending order — A→Z, 1→100, oldest→newest
• DESC: Descending order — Z→A, 100→1, newest→oldest

Sorting by Multiple Columns

You can sort by multiple columns. The database sorts by the first column, then uses subsequent columns as tiebreakers.

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

This sorts employees by department (alphabetically), and within each department, by salary (highest first).

How NULL Values Sort

NULLs can be tricky. Different databases handle NULL sorting differently:

Database	NULL Position (ASC)	NULL Position (DESC)
SQLite	Last	First
PostgreSQL	Last	First
MySQL	First	Last
SQL Server	First	Last

Some databases support NULLS FIRST and NULLS LAST to control this explicitly:

-- PostgreSQL, Oracle, SQLite 3.30+
SELECT * FROM orders
ORDER BY shipping_date ASC NULLS LAST;

Sorting by Column Position

You can also sort by column position (the number of the column in your SELECT list). This is useful but less readable:

-- Sort by the 3rd column (salary)
SELECT name, department, salary
FROM employees
ORDER BY 3 DESC;

Pro tip: Avoid this in production code — it breaks if you change your SELECT columns.

LIMIT: Restricting Result Size

LIMIT restricts how many rows are returned. It's essential for:

• Pagination (showing page 1, page 2, etc.)
• Top-N queries (top 10 products, latest 5 orders)
• Testing queries on large tables

-- Get the top 5 highest-paid employees
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

OFFSET: Skipping Rows for Pagination

OFFSET tells the database how many rows to skip before returning results. Combined with LIMIT, it enables pagination.

-- Page 1: first 10 rows
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Page 2: rows 11-20
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

-- Page 3: rows 21-30
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

Syntax Variations Across Databases

Different databases have slightly different syntax:

-- MySQL, PostgreSQL, SQLite
SELECT * FROM products LIMIT 10 OFFSET 20;
-- Or
SELECT * FROM products LIMIT 10, 20; -- MySQL only (offset, limit)

-- SQL Server
SELECT TOP 10 * FROM products;
-- With offset (SQL Server 2012+)
SELECT * FROM products ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle
SELECT * FROM products FETCH FIRST 10 ROWS ONLY; -- Oracle 12c+

The Performance Problem with Large OFFSET

Here's something many developers don't realize: large OFFSET values are slow.

-- This is SLOW on large tables!
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;

Why? The database still has to read and skip 1,000,000 rows before returning your 10 results.

Better Approaches for Deep Pagination

1. Keyset Pagination (Cursor-based)

Instead of OFFSET, use the last seen value:

-- First page
SELECT * FROM orders ORDER BY id LIMIT 10;

-- Next page (if last id was 12345)
SELECT * FROM orders WHERE id > 12345 ORDER BY id LIMIT 10;

2. Show Limited Pages

Google doesn't let you jump to page 1,000,000. Consider limiting how deep users can paginate.

Common Use Cases

Top-N Queries

-- Top 10 customers by spending
SELECT customer_name, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_name
ORDER BY total_spent DESC
LIMIT 10;

Latest Records

-- 5 most recent orders
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5;

Random Selection

-- Get 3 random products (SQLite)
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 3;

Best Practices

1. Always use ORDER BY with LIMIT — Without ORDER BY, the order is undefined and unpredictable

2. Index your ORDER BY columns — Especially for large tables with frequent sorting

3. Avoid large OFFSET values — Use keyset pagination for deep pagination

4. Be explicit with ASC/DESC — Makes your intent clear to other developers

5. Consider NULL handling — Test how NULLs sort and use NULLS FIRST/LAST if available

Related Articles

• Optimizing Large Dataset Queries for the scaling problems that appear when sorting and pagination hit real row counts.
• Understanding Database Indexes: The Key to Performance for the access-path reasoning behind fast ordered reads.
• Reading SQL Execution Plans for verifying whether the database is sorting efficiently or spilling into expensive plans.

Conclusion

ORDER BY and LIMIT are fundamental SQL clauses that you'll use in almost every real-world application:

• ORDER BY sorts results by one or more columns (ASC or DESC)
• LIMIT restricts the number of rows returned
• OFFSET skips rows for pagination
• Beware of performance with large OFFSET values — consider keyset pagination

With these tools, you can build efficient pagination, top-N queries, and present data in exactly the order your users expect.

Try the interactive examples above to practice these concepts!