Filter and sort

Opening your lesson

WHERE keeps the rows that pass a test: =, !=, <, >=, IN ('a', 'b'), BETWEEN low AND high (both ends included) and IS NULL.

Text comparisons are case-sensitive, and so is LIKE. BigQuery has no ILIKE: lowercase the column first, as in LOWER(name) LIKE '%espresso%'. Strings can use single or double quotes; dates are written '2025-03-01' or DATE '2025-03-01'.

ORDER BY sorts, DESC reverses and LIMIT n keeps the first n rows. One BigQuery default differs from many databases: NULLs come first when sorting up and last when sorting down. Add NULLS LAST or NULLS FIRST to choose.

LIMIT doesn't make a query cheaper. BigQuery still reads every row of the columns you name, then returns fewer.

SELECT order_id, order_date, status
FROM shop.orders
WHERE status IN ('refunded', 'processing')
ORDER BY order_date DESC
LIMIT 10;

The ten most recent orders that were refunded or are still processing.

Choose your character

Your Work Gym account