Filter and sort

Opening your lesson

WHERE keeps the rows that pass a test. Compare with =, <>, < and >=, list values with IN ('a', 'b'), and test ranges with BETWEEN, which includes both ends. Missing values need IS NULL, because = NULL is never true.

Careful with BETWEEN on timestamps. ORDER_TS BETWEEN '2025-03-01' AND '2025-03-31' stops at midnight at the start of the 31st, so most of that day is lost. For a whole month, write order_ts >= '2025-03-01' AND order_ts < '2025-04-01'.

ORDER BY sorts, DESC puts the largest first, and LIMIT n keeps the first n rows. Snowflake also accepts SELECT TOP n. NULLs sort last going up and first going down; say NULLS FIRST or NULLS LAST to choose.

SELECT order_id, order_total
FROM orders
WHERE status IN ('returned', 'cancelled')
ORDER BY order_total DESC
LIMIT 5;

The five largest orders that were returned or cancelled.

Choose your character

Your Work Gym account