ORDER BY and LIMIT in MySQL

Without explicit ordering, MySQL does not guarantee the order of rows in a result set. The ORDER BY clause lets you sort results by one or more columns, while LIMIT controls how many rows are returned. Together they form the backbone of pagination and top-N queries.

Sorting with ORDER BY

Place ORDER BY at the end of your SELECT statement followed by the column name. The default direction is ascending (ASC). Use DESC for descending order.

-- Cheapest products first
SELECT product_name, price
FROM products
ORDER BY price ASC;

-- Newest orders first
SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC;

Sorting by Multiple Columns

When you sort by more than one column, MySQL applies the first sort and then breaks ties using the second column, and so on. Each column can have its own direction.

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

Limiting Results with LIMIT

LIMIT restricts the number of rows returned. This is crucial for performance when you only need a subset of data and for building paginated interfaces in applications.

-- Top 5 highest-paid employees
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Pagination with LIMIT and OFFSET

OFFSET skips a specified number of rows before returning results. Combined with LIMIT, this creates pages of data. Page 1 uses OFFSET 0, page 2 uses OFFSET equal to the page size, and so on.

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

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

Sorting by Expressions and Aliases

You can sort by computed expressions or by column aliases defined in the SELECT list. You can also reference columns by their ordinal position, though this is generally less readable.

SELECT product_name, price * quantity AS total_value
FROM products
ORDER BY total_value DESC
LIMIT 10;

Performance Tip

Large OFFSET values can be slow because MySQL still reads and discards the skipped rows. For high-performance pagination on large tables, consider keyset pagination (using a WHERE clause on the last seen ID) instead of OFFSET-based pagination.