Sorting and Limiting Results
Sort results with ORDER BY, keep the first few rows with LIMIT, page with OFFSET and add a tie-breaker for a fully decided order.
In this lesson you will
- Sort by one or more columns
- Keep the top few rows with LIMIT
- Page through results with OFFSET
- Add a tie-breaker for a decided order
About 22 minutes
“Show me the best sellers first”
A list in random order is a poor report. Maya wants the most expensive products at the top, and the three cheapest as a bargain list. Two clauses do this. ORDER BY sorts the rows, and LIMIT cuts the result down to the first few. Both come at the end of a query, after any WHERE.
ORDER BY
ORDER BY takes one or more columns. Rows are sorted by the first column, and ties are broken by the second, and so on. The default is ascending, smallest first, and DESC reverses it. Text sorts alphabetically, dates written in year-month-day form sort in date order, and where NULLs appear depends on the database, so check how yours treats them when a column has missing values.
LIMIT and OFFSET
LIMIT 3 keeps the first three rows of the sorted result. OFFSET 3 skips three rows before starting, which is how a website shows page two of a list. LIMIT on its own with no ORDER BY is risky, because ‘the first three’ of an unordered list is arbitrary. Always pair them. Be aware that LIMIT is not part of every database. Some write the same idea as TOP or as FETCH FIRST. The idea is the same, but the words differ, so check your system.
Five sorted queries
-- 1
SELECT name, price FROM products ORDER BY price DESC
-- 2
SELECT name, category, price FROM products ORDER BY category, price DESC
-- 3
SELECT name, price FROM products ORDER BY price DESC LIMIT 3
-- 4
SELECT name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 3
-- 5
SELECT id, customer_id, order_date FROM orders ORDER BY order_date DESC LIMIT 2
-- 1
name | price
Orchid | 18.00
Glass pot | 15.00
Fern | 12.50
Succulent mix | 9.00
Clay pot | 8.50
Cactus | 6.00
Plant food | 5.50
Mist spray | 4.00
-- 2
name | category | price
Plant food | care | 5.50
Mist spray | care | 4.00
Orchid | plant | 18.00
Fern | plant | 12.50
Succulent mix | plant | 9.00
Cactus | plant | 6.00
Glass pot | pot | 15.00
Clay pot | pot | 8.50
-- 3
name | price
Orchid | 18.00
Glass pot | 15.00
Fern | 12.50
-- 4
name | price
Succulent mix | 9.00
Clay pot | 8.50
Cactus | 6.00
-- 5
id | customer_id | order_date
10 | 3 | 2026-10-20
9 | 4 | 2026-10-02
Query 1 lists every product, dearest first: it starts with the Orchid and ends with the Mist spray. Query 2 sorts by category first and price second, so the category names run in alphabetical order, and inside each category the prices fall. The first category is care. Query 3 takes the top three by price: Orchid, Glass pot, Fern. Query 4 skips those three and shows the next three: Succulent mix, Clay pot, Cactus. That is page two if the page size is three. Query 5 sorts orders by date, newest first, and keeps two: orders 10, 9.
Ties need a tie-breaker
In this table every price is different, so each sort has one correct answer. In real data, ties are common. Suppose two products cost the same and you ask for the top one only. Which does the database choose? Either, and not necessarily the same one next time. The cure is simple: add a second sort column that is unique, such as the id or the name, so that the order is always fully decided. A paged list without a tie-breaker can even show the same product on two pages or skip one altogether.
Habits for sorted results
- Write ORDER BY every time order matters.
- Add a unique column as a final tie-breaker.
- Keep LIMIT for the end of the query, after ORDER BY.
- When you page through results, keep the ORDER BY identical on every page.
Taking the first rows of an unsorted result and calling them the ‘top’ ones. Without ORDER BY there is no top, only whatever the database returned first.
The products and orders are made up. Row limiting is written differently in different databases, such as LIMIT, TOP or FETCH FIRST, and the placement of NULLs in a sort also differs, so check your documentation.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does ORDER BY price DESC do?
- Sorts with the lowest price first
- Removes duplicates
- Sorts with the highest price first
- Counts the prices
DESC reverses the default ascending order.
What does LIMIT 3 do?
- Keeps the first three rows of the result
- Skips three rows
- Sorts three columns
- Deletes three rows
It cuts the result down to three rows.
What does OFFSET 3 do?
- Keeps only three rows
- Skips the first three rows
- Adds three rows
- Rounds to three
Query 4 showed the products after the first three.
Why add a second sort column?
- To make the query faster
- To hide NULLs
- To remove duplicates
- To decide ties, so the order is always fully set
Tied rows can come back in any order without a tie-breaker.
Why is LIMIT without ORDER BY risky?
- LIMIT is not valid alone
- The first rows of an unordered result are arbitrary
- It deletes data
- It sorts by id
There is no top without an ordering.
Summary
- ORDER BY sorts; DESC reverses.
- LIMIT and OFFSET cut and page a sorted result.
- Always sort before limiting.
- Add a unique tie-breaker.
Questions
Does every database support LIMIT?
No. Some use TOP or FETCH FIRST for the same idea. Check your system's documentation.
Where do NULLs go in a sort?
It depends on the database. Some put them first, others last. Check how yours behaves when a column has missing values.
Can I sort by a calculated column?
Yes. You can sort by an alias or by the calculation itself, depending on the database.
