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.
ORDER BYexpressionASCsorts from low to high andDESCsorts from high to low.ASCis the default.- Two rows with the same value in the sort key are a tie, and their order is then whatever the engine returns. A second sort key decides a tie the same way every time, as in
ORDER BY odometer.value_normalized_km DESC, vehicle_id ASC. NULLis not a number, so there is no place for it among the numbers. BigQuery puts it at one end of the sort, and the direction decides which end. Other databases made the opposite choice, so a habit from PostgreSQL will mislead you here.LIMIT nruns after the sort and returns at mostnrows.
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 3Tests 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.