LIMIT

01The problem

"What were our five most expensive shipments ever?"

You can sort all 2,683 shipments by charge and read the top five. But when a report only needs the top five, returning thousands of rows wastes time, and on a large database it can slow everyone down.

02The concept

LIMIT keeps only the first rows of a result. On its own, "the first rows" means whatever the database happens to return first, which can be any rows. Combined with ORDER BY, it answers the questions managers ask all the time: the top five, the ten most recent, the cheapest three.

The syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT number OFFSET skip;

LIMIT goes at the very end. The full order of the clauses so far:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ... OFFSET ...

Top N: ORDER BY, then LIMIT

The database sorts first and then cuts:

SELECT shipment_id, booking_date, freight_charge
FROM shipments
ORDER BY freight_charge DESC
LIMIT 5;

1. ORDER BY freight_charge DESC sorts all 2,683 shipments, highest charge first.
2. LIMIT 5 keeps the first five rows of that sorted list.

Change the direction and the same pattern gives the bottom five:

SELECT shipment_id, booking_date, freight_charge
FROM shipments
WHERE status = 'Delivered'
ORDER BY freight_charge
LIMIT 5;

LIMIT without ORDER BY

Leave out ORDER BY and you get five rows, but not five meaningful ones:

SELECT shipment_id, freight_charge
FROM shipments
LIMIT 5;

That's useful for one thing: a quick look at a table you don't know yet, without returning thousands of rows.

SELECT *
FROM payments
LIMIT 10;

Ties at the cut-off

What if several rows share the value where the cut falls? 112 shipments carried 8 containers, the most of any. So this query returns five of them, but which five isn't defined:

SELECT shipment_id, containers
FROM shipments
ORDER BY containers DESC
LIMIT 5;

Add a tie-breaker to make the answer the same every time:

SELECT shipment_id, containers, booking_date
FROM shipments
ORDER BY containers DESC, booking_date
LIMIT 5;

Now it's "the five earliest 8-container shipments", which is a question with one answer. When someone asks for "the top five", check whether ties are possible, and say how you broke them.

Skipping rows: OFFSET

OFFSET n skips the first n rows before LIMIT starts counting. Apps use it to show results in pages:

The second page of customers, in signup order:

SELECT company_name, signup_date
FROM customers
ORDER BY signup_date
LIMIT 10 OFFSET 10;

OFFSET also answers "the second highest" or "the third most recent":

SELECT shipment_id, freight_charge
FROM shipments
ORDER BY freight_charge DESC
LIMIT 1 OFFSET 1;

That skips the most expensive shipment and returns the next one.

The same idea in other databases

LIMIT is SQLite, MySQL and PostgreSQL. Other databases write it differently:

The idea is identical: sort, then keep the first rows.

03Example

Back to the question: "What were our five most expensive shipments ever?"

SELECT shipment_id, booking_date, containers, freight_charge
FROM shipments
ORDER BY freight_charge DESC
LIMIT 5;

And a related one from finance: "Which five delivered shipments in 2026 had the lowest charge per container?"

SELECT
  shipment_id,
  containers,
  freight_charge / containers AS charge_per_container
FROM shipments
WHERE status = 'Delivered'
  AND booking_date >= '2026-01-01'
ORDER BY charge_per_container
LIMIT 5;

04Walkthrough

The second query uses every clause you've learned, in order:

1. FROM shipments: start with every shipment.
2. WHERE status = 'Delivered' AND booking_date >= '2026-01-01': keep delivered shipments booked in 2026.
3. SELECT: show three columns, one of them calculated and named charge_per_container.
4. ORDER BY charge_per_container: sort the remaining rows, lowest first. The alias works here because ORDER BY runs after SELECT.
5. LIMIT 5: keep the first five.

Common mistakes

Summary

05Practice

06Challenge

07More practice

Optional drills on the same skills. They don't count towards the certificate, but each one is a small, realistic request from someone at Harbourline. Do as many as you need until the pattern feels automatic.

08Check your understanding