Window Functions

The OVER clause can be used with the aggregate and ranking functions to include information about the group of records alongside each individual row.

SQL 4 CDS aims to support all the options within the OVER clause except:

• RANGE
• UNBOUNDED FOLLOWING

Examples

Generate a running total using the SUM function with an OVER clause that includes all the rows up to the current row:

SELECT   name,
         revenue,
         SUM(revenue) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
FROM     account
ORDER BY name