ORDER BY and LIMIT

Rankings such as the highest mileage or the newest test appear in almost every analysis. ORDER BY and LIMIT are how you ask for them.

How it works

ORDER BY first puts the rows into a fixed sequence. LIMIT then keeps only the first rows of that sequence. LIMIT does not lower the bytes BigQuery estimates for the query.

Common mistake

Forgetting DESC turns a top-N question into a bottom-N answer, and LIMIT without ORDER BY returns an arbitrary set of rows.

Worked example

SELECT vehicle_id, completed_date, odometer.value_normalized_km
FROM `learn.mot.fact_mot_test`
WHERE make = 'TOYOTA'
  AND completed_date BETWEEN '2024-05-01' AND '2024-05-31'
ORDER BY completed_date DESC, vehicle_id ASC
LIMIT 3

Tests are sorted by date with the newest first, and by vehicle within a date.

odometer is a STRUCT, a record that holds several related fields in one column. Reach a field with a dot, as in odometer.value_normalized_km.

LIMIT keeps the first three rows of that order. The dry run still estimates the bytes of every May row.

All BigQuery lessons