SQL ORDER BY and LIMIT

SQL's ORDER BY clause sorts query results, and LIMIT caps how many rows the database returns. Together, they answer questions like "Which five products cost the most?" Sort direction and tie-breakers determine the order; without ORDER BY, LIMIT doesn't define which rows you'll get.

This article covers the "Structuring" chapter of Learn SQL, where you can practice these queries in your browser.

How Does SQL ORDER BY Work?

SQL offers us the ability to sort the results of a query using ORDER BY. By default, the ORDER BY keyword sorts records by the given field in ascending order, or ASC for short. However, ORDER BY does support descending order as well with the keyword DESC.

This query returns the name, price, and quantity fields from the products table sorted by price in ascending order:

SELECT name, price, quantity FROM products
ORDER BY price;

The cheapest product appears first. In a query, ORDER BY goes after FROM and any WHERE clause, and writing ORDER BY price ASC makes the same instruction explicit.

ASC and DESC Sort Direction

Use ASC for the smallest value first and DESC for the largest value first. For numbers, that means low to high or high to low. For text, it generally means alphabetical or reverse alphabetical order according to the database's configured collation.

This query returns the name, price, and quantity of the products ordered by quantity in descending order:

SELECT name, price, quantity FROM products
ORDER BY quantity DESC;

The product with the largest inventory appears first. Dates follow the same rule: ORDER BY created_at DESC puts the newest timestamps first.

The database controls text collation and where null values appear. PostgreSQL, MySQL, and SQLite share the basic syntax but differ around those details, so check your database's documentation when they affect your result.

Sorting by Multiple Columns

Separate sort columns with commas. SQL sorts by the first column, then uses the second column when rows have the same value in the first:

SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

Products are grouped alphabetically by category. Within each category, the most expensive product appears first. Each column can have its own direction.

Multiple sort columns make results deterministic only when the full sort key uniquely orders every row. If several products have the same price, sorting by price alone doesn't define their order relative to each other. Add a unique column as the final tie-breaker:

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC;

Deterministic ordering is important in APIs, tests, and pagination. Stable identifiers make useful tie-breakers, whether the table uses an integer ID or a UUID.

SQL LIMIT Syntax

Sometimes we don't want to retrieve every record from a table. For example, it's common for a production database table to have millions of rows, and SELECTing all of them might crash your system! The LIMIT keyword has entered the chat.

LIMIT can be used at the end of a SELECT statement to set a cap on the number of records returned.

SELECT * FROM products
WHERE product_name LIKE '%berry%'
LIMIT 50;

The query above uses the LIKE operator to retrieve all the records from the products table where the name contains the word berry. If we ran this query on the Amazon database, it would almost certainly return a lot of records.

The LIMIT clause only allows the database to return up to 50 records matching the query. This means that if there aren't that many records matching the query, LIMIT will not have an effect. Beyond protecting you from accidentally retrieving a huge table, LIMIT is useful for samples and small API responses.

Without ORDER BY, the database can return any 50 matching rows. Their order may change as rows are inserted, deleted, or processed with a different query plan. Add ORDER BY whenever the identity of the limited rows matters.

Using ORDER BY and LIMIT Together

When using both ORDER BY and LIMIT, the ORDER BY clause must come first. The database sorts the matching result set, then returns the requested number of rows:

SELECT id, name, price
FROM products
WHERE quantity > 0
ORDER BY price DESC, id ASC
LIMIT 5;

This is a top-N query: it returns the five most expensive products that are in stock. Reversing the direction returns the bottom five instead.

The order of SQL clauses is fixed even though databases don't necessarily execute each operation in the order you type it. For this query shape, use:

SELECT columns
FROM table_name
WHERE condition
ORDER BY sort_column
LIMIT row_count;

Backend web development is full of top-N queries. Return only the rows the caller needs, in an order it can rely on.

When Should You Use LIMIT and OFFSET?

OFFSET skips rows before LIMIT returns a page:

SELECT id, name, created_at
FROM products
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

This query skips the first 40 sorted rows and returns the next 20. With 20 rows per page, that corresponds to the third page.

Basic offset pagination is easy to understand. Large offsets can be slow because the database still has to find and skip earlier rows. Inserts and deletes between requests can also repeat or omit results. Use cursor-based pagination for large or frequently changing datasets; LIMIT and OFFSET are fine for small result sets and internal tools.

Pagination syntax varies by database. PostgreSQL, MySQL, and SQLite support LIMIT with OFFSET; SQL Server commonly uses OFFSET ... FETCH, and standard SQL uses FETCH FIRST. Check your database's SELECT documentation before moving a query between dialects.