This guide is specifically for MySQL syntax.

SQL Window Functions Reference Guide

Complete guide to ROW_NUMBER, RANK, LAG, LEAD, and frame specifications.

Basic Syntax

Syntax:

function() OVER ([PARTITION BY col] [ORDER BY col] [frame])

Window functions perform calculations across related rows without grouping.

Example:

SELECT name, salary, SUM(salary) OVER () as total FROM employees;

💡 Note: OVER() is required for window functions

ROW_NUMBER

Syntax:

ROW_NUMBER() OVER (ORDER BY column)

Assigns unique sequential numbers starting from 1.

Example:

SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;

💡 Note: Always unique, no ties

RANK / DENSE_RANK

Syntax:

RANK() OVER (ORDER BY column) | DENSE_RANK() OVER (ORDER BY column)

RANK skips numbers after ties (1,2,2,4). DENSE_RANK doesn't (1,2,2,3).

Example:

SELECT name, RANK() OVER (ORDER BY score DESC) as rank FROM students;

💡 Note: Use DENSE_RANK when you don't want gaps

LAG / LEAD

Syntax:

LAG(col, offset, default) OVER (ORDER BY col) | LEAD(...)

LAG accesses previous row, LEAD accesses next row.

Example:

SELECT date, value, LAG(value, 1, 0) OVER (ORDER BY date) as prev FROM data;

💡 Note: Offset defaults to 1, default is NULL

PARTITION BY

Syntax:

function() OVER (PARTITION BY col ORDER BY col)

Divides rows into groups for separate window calculations.

Example:

SELECT dept, name, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) FROM employees;

💡 Note: Like GROUP BY but keeps all rows

Frame Specification

Syntax:

ROWS BETWEEN start AND end | RANGE BETWEEN start AND end

Defines which rows to include in the window frame.

Example:

SELECT date, AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) FROM data;

💡 Note: UNBOUNDED PRECEDING/FOLLOWING, n PRECEDING/FOLLOWING, CURRENT ROW

FIRST_VALUE / LAST_VALUE

Syntax:

FIRST_VALUE(col) OVER (...) | LAST_VALUE(col) OVER (...)

Returns first or last value in the window frame.

Example:

SELECT name, FIRST_VALUE(name) OVER (ORDER BY salary DESC) as top_earner FROM employees;

💡 Note: LAST_VALUE needs proper frame definition

Quick Reference Table

| Function | Purpose | |----------|---------| | Basic Syntax | Window functions perform calculations across related rows without grouping | | ROW_NUMBER | Assigns unique sequential numbers starting from 1 | | RANK / DENSE_RANK | RANK skips numbers after ties (1,2,2,4) | | LAG / LEAD | LAG accesses previous row, LEAD accesses next row | | PARTITION BY | Divides rows into groups for separate window calculations | | Frame Specification | Defines which rows to include in the window frame | | FIRST_VALUE / LAST_VALUE | Returns first or last value in the window frame |

Related Cheat Sheets

Continue learning with more SQL references:

MySQL-Specific Notes

This page covers MySQL syntax. Other databases may have different syntax for similar operations.