max_
On this page
Returns a value from the row with the maximum comparison key.
Syntax
max_by(<value>, <key>)Arguments
| Argument | Type | Description |
|---|---|---|
<value> | Any type | Expression to return from the selected row. May be NULL. |
<key> | Any type except any | Expression used to select the row. NULL keys are ignored. |
Returns
Returns the same type as <value>. Returns NULL if there is no non-NULL key. If keys tie, any tied row may be selected.
Examples
Keep the details of the slowest request
Find the slowest request per service and retain its ID and status together in one object.
VALUES
('checkout', 'req-101', 200, 95), ('checkout', 'req-102', 504, 1200),
('catalog', 'req-201', 200, 25), ('catalog', 'req-202', 200, 140)
SELECT $0 AS service, $1 AS request_id, $2 AS status, $3 AS latency_ms
GROUP BY service
AGGREGATE max_by({'request_id': request_id, 'status': status}, latency_ms) AS request
ORDER BY service+----------+---------------------------------------+
| service | request |
+----------+---------------------------------------+
| catalog | {"request_id":"req-202","status":200} |
| checkout | {"request_id":"req-102","status":504} |
+----------+---------------------------------------+Read the latest order status
Use the event timestamp as the comparison key to retrieve the latest known state of each order.
VALUES
('o-17', '2026-06-01T10:02:00Z', 'paid'),
('o-17', '2026-06-01T10:00:00Z', 'created'),
('o-42', '2026-06-01T10:01:00Z', 'created'),
('o-42', '2026-06-01T10:04:00Z', 'cancelled')
SELECT $0 AS order_id, $1::timestamp AS event_time, $2 AS status
GROUP BY order_id
AGGREGATE max_by(status, event_time) AS latest_status
ORDER BY order_id+----------+---------------+
| order_id | latest_status |
+----------+---------------+
| o-17 | paid |
| o-42 | cancelled |
+----------+---------------+If events share the latest timestamp, either status may be returned. Use DISTINCT ON ... BY with an additional sort key when ties need a defined order.