Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

MAX Window Function Examples

Show the sales ID, quantity, and maximum quantity from the beginning of the data window:

Copy
select salesid, qty, max(qty) over (order by salesid rows unbounded preceding) as max from winsales order by salesid; salesid | qty | max ---------+-----+----- 10001 | 10 | 10 10005 | 30 | 30 10006 | 10 | 30 20001 | 20 | 30 20002 | 20 | 30 30001 | 10 | 30 30003 | 15 | 30 30004 | 20 | 30 30007 | 30 | 30 40001 | 40 | 40 40005 | 10 | 40 (11 rows)

For a description of the WINSALES table, see Window Function Examples.

Show the salesid, quantity, and maximum quantity in a restricted frame:

Copy
select salesid, qty, max(qty) over (order by salesid rows between 2 preceding and 1 preceding) as max from winsales order by salesid; salesid | qty | max ---------+-----+----- 10001 | 10 | 10005 | 30 | 10 10006 | 10 | 30 20001 | 20 | 30 20002 | 20 | 20 30001 | 10 | 20 30003 | 15 | 20 30004 | 20 | 15 30007 | 30 | 20 40001 | 40 | 30 40005 | 10 | 40 (11 rows)