WINDOW FUNCTIONSQuery Techniques
Buy Me A Coffee

Perform calculations across a set of rows related to the current row.

Perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY does. Essential for running totals, rankings, moving averages, and row comparisons. Not supported by MySQL before 8.0.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 Added in MySQL 8.0
PostgreSQL ✓ Supported 8.4 Supported since PG 8.4
SQL Server ✓ Supported 2005 Since SQL Server 2005
Oracle ✓ Supported 8i Since Oracle 8i
SQLite ✓ Supported 3.25.0 Added in SQLite 3.25.0

Details

Includes ranking, aggregate, and value functions like LEAD and LAG.

Standard Syntax

SELECT column1, AVG(column3) OVER (PARTITION BY column1 ORDER BY column2) AS avg_val FROM table_name;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2005 Oracle: Since 8i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

Added in MySQL 8.0

SELECT col1, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS rn FROM tbl;

PostgreSQL

Supported since PG 8.4

SELECT col1, RANK() OVER (PARTITION BY col1 ORDER BY col2) AS rnk FROM tbl;

SQL Server

Since SQL Server 2005

SELECT col1, LAG(col2) OVER (PARTITION BY col1 ORDER BY col2) AS prev_val FROM tbl;

Oracle

Since Oracle 8i

SELECT col1, FIRST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2) AS first_val FROM tbl;

SQLite

Added in SQLite 3.25.0

SELECT col1, SUM(col2) OVER (PARTITION BY col1) AS total FROM tbl;