Combining WHERE, ORDER BY and LIMIT in SQL

Lesson 2.8 · Reading time: ~6 min · Practice tasks ↓

Most real SQL queries combine WHERE, ORDER BY and LIMIT: they filter rows, sort them and return only the part that is needed. In this lesson you'll learn the required order of these clauses, the order in which the database actually processes them, and how to write filtered top-N and pagination queries.

In this module you have learned to filter rows with WHERE, sort them with ORDER BY and restrict the result with LIMIT. Separately, each clause solves a small task. Together they answer typical business questions: "the 5 cheapest films to replace", "the latest payments over $10", "page 2 of the search results".

The Written Order of Clauses

The clauses of a SELECT query must be written in a fixed order. In any other order, the database returns a syntax error.

SELECT columns
FROM table_name
WHERE condition
ORDER BY sort_columns
LIMIT row_count OFFSET skip_count;

1. SELECT: which columns to return.
2. FROM: which table to read.
3. WHERE: which rows to keep.
4. ORDER BY: how to sort them.
5. LIMIT / OFFSET: which part of the sorted result to return.

This is the order for the clauses you know so far; GROUP BY and HAVING will take their places between WHERE and ORDER BY in Module 4.

The Logical Processing Order

The database doesn't process a query in the order it is written. Logically, the steps are:

This order explains two important rules:

• WHERE can't use column aliases from SELECT: at step 2 they don't exist yet.
• LIMIT is applied after filtering and sorting, so "top 5" means the top 5 of the filtered rows, not 5 random rows that are then filtered.

The optimizer can execute a query differently internally, but the result is always the same as if these steps were performed in this order.

Filtered Top-N Queries

The Five Shortest Cheap Films

SELECT title, length, replacement_cost
FROM film
WHERE replacement_cost < 15.00
ORDER BY length, title
LIMIT 5;

Result: the 5 shortest films that cost less than $15 to replace.

The Latest Large Payments

SELECT payment_id, customer_id, amount, payment_date
FROM payment
WHERE amount >= 10.00
ORDER BY payment_date DESC
LIMIT 10;

Result: the 10 most recent payments of $10 or more.

Actors by a Letter

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'B%'
ORDER BY first_name, last_name
LIMIT 3;

Result: the first 3 actors, in order of first name, whose last name starts with "B".

Pagination of a Filtered List

Applications usually paginate a filtered and sorted list, such as search results. All three clauses are needed:

1. WHERE selects the rows the user is looking for.
2. ORDER BY fixes the order, so rows don't repeat or disappear between pages.
3. LIMIT and OFFSET choose the page: LIMIT S OFFSET (N - 1) * S.

SELECT first_name, last_name
FROM actor
WHERE first_name LIKE 'A%'
ORDER BY last_name, first_name
LIMIT 5 OFFSET 5;

Result: page 2 (rows 6 to 10) of the actors whose first name starts with "A", sorted by last name.

Key Takeaways:

• The written order is SELECT → FROM → WHERE → ORDER BY → LIMIT.
• The logical order is FROM → WHERE → SELECT → ORDER BY → LIMIT.
• WHERE is applied before SELECT, so it can't use column aliases; ORDER BY can.
• LIMIT takes rows from the already filtered and sorted result.
• For stable top-N lists and pagination, make the sort unique.

Frequently Asked Questions

What is the correct order of WHERE, ORDER BY and LIMIT in SQL?

WHERE comes first, then ORDER BY, then LIMIT: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT .... Any other order causes a syntax error.

In what order does SQL execute a query?

Logically: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This is why WHERE can't use aliases from SELECT, while ORDER BY can.

Is LIMIT applied before or after WHERE?

After. The database first filters rows with WHERE, sorts them with ORDER BY, and only then returns the number of rows set by LIMIT.