Module 6 · SQL for Data Analysis
LIMIT
Return only the first rows of a result to answer "top N" questions and page through data.
About 15 minutes
The 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.
The concept
LIMIT n returns only the first n rows of the result. It goes at the very end of the query.
On its own, LIMIT just takes whichever rows come first, which may be any rows. Combined with ORDER BY, it answers "top N" and "bottom N" questions.
OFFSET skips rows before the limit starts. LIMIT 10 OFFSET 10 returns rows 11 to 20, which is how apps show "page 2".
Example
SELECT shipment_id, booking_date, freight_charge
FROM shipments
ORDER BY freight_charge DESC
LIMIT 5;Walkthrough
ORDER BY freight_charge DESCsorts every shipment, highest charge first.LIMIT 5keeps the first five rows of that sorted result.
The order matters: the database sorts first, then cuts. If you left out ORDER BY, you'd get five random shipments, not the top five.
Paging through results works the same way:
SELECT company_name, signup_date
FROM customers
ORDER BY signup_date
LIMIT 10 OFFSET 10;This skips the ten earliest customers and shows the next ten.
Practice
Practice
Show the shipment_id, containers and weight_kg of the 5 heaviest shipments.
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Challenge
Challenge · optional
Show the 3 air routes with the shortest target_transit_days. Include origin, destination and target_transit_days. Break ties by route_id.
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Check your understanding
Answer every question to check.