Window Functions

A window function performs an aggregate-like operation over a set of query rows, but unlike an aggregate function (see Aggregate), which collapses a group into a single result row, a window function produces one result per query row.

SELECT SUM(profit) AS total_profit FROM sales;
→ a single row: 7535

SELECT country, SUM(profit) AS country_profit
FROM sales GROUP BY country ORDER BY country;
→ one row per country: Finland 1610, India 1350, USA 4575

SELECT year, country, product, profit,
  SUM(profit) OVER() AS total_profit,
  SUM(profit) OVER(PARTITION BY country) AS country_profit
  FROM sales ORDER BY country, year, product;
→ every original row is preserved, each carrying both the grand total (7535) and its country's subtotal.

CREATE TABLE sales (year INT, country VARCHAR(20), product VARCHAR(20), profit INT);
INSERT INTO sales VALUES
    (2000,'Finland','Computer',1500), (2000,'Finland','Phone',100),
    (2001,'Finland','Phone',10),
    (2000,'India','Calculator',75), (2000,'India','Calculator',75),
    (2000,'India','Computer',1200),
    (2000,'USA','Calculator',75), (2000,'USA','Computer',1500),
    (2001,'USA','Calculator',50), (2001,'USA','Computer',1500),
    (2001,'USA','Computer',1200), (2001,'USA','TV',150), (2001,'USA','TV',100);

SELECT SUM(profit) AS total_profit FROM sales;

SELECT country, SUM(profit) AS country_profit
    FROM sales GROUP BY country ORDER BY country;

SELECT year, country, product, profit,
    SUM(profit) OVER() AS total_profit,
    SUM(profit) OVER(PARTITION BY country) AS country_profit
    FROM sales
    ORDER BY country, year, product, profit;

CREATE TABLE sales (year INT, country VARCHAR(20), product VARCHAR(20), profit INT);
INSERT INTO sales VALUES
    (2000,'Finland','Computer',1500), (2000,'Finland','Phone',100),
    (2001,'Finland','Phone',10),
    (2000,'India','Calculator',75), (2000,'India','Calculator',75),
    (2000,'India','Computer',1200),
    (2000,'USA','Calculator',75), (2000,'USA','Computer',1500),
    (2001,'USA','Calculator',50), (2001,'USA','Computer',1500),
    (2001,'USA','Computer',1200), (2001,'USA','TV',150), (2001,'USA','TV',100);

SELECT SUM(profit) AS total_profit FROM sales;

SELECT country, SUM(profit) AS country_profit
    FROM sales GROUP BY country ORDER BY country;

SELECT year, country, product, profit,
    SUM(profit) OVER() AS total_profit,
    SUM(profit) OVER(PARTITION BY country) AS country_profit
    FROM sales
    ORDER BY country, year, product, profit;

SELECT SUM(profit) AS total_profit FROM sales:

total_profit
7535

SELECT country, SUM(profit) AS country_profit FROM sales GROUP BY country ORDER BY country:

country	country_profit
Finland	1610
India	1350
USA	4575

SELECT year, country, product, profit, SUM(profit) OVER() AS total_profit, SUM(profit) OVER(PARTITION BY country) AS country_profit FROM sales ORDER BY country, year, product, profit:

year	country	product	profit	total_profit	country_profit
2000	Finland	Computer	1500	7535	1610
2000	Finland	Phone	100	7535	1610
2001	Finland	Phone	10	7535	1610
2000	India	Calculator	75	7535	1350
2000	India	Calculator	75	7535	1350
2000	India	Computer	1200	7535	1350
2000	USA	Calculator	75	7535	4575
2000	USA	Computer	1500	7535	4575
2001	USA	Calculator	50	7535	4575
2001	USA	Computer	1200	7535	4575
2001	USA	Computer	1500	7535	4575
2001	USA	TV	100	7535	4575
2001	USA	TV	150	7535	4575

(adapted from https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html)

Window-Only Functions

MySQL also supports nonaggregate functions that exist only as window functions, for which the OVER clause is mandatory:

CUME_DIST() returns the cumulative distribution of a value within its partition — the number of partition rows preceding or peer with the current row, divided by the total number of partition rows. Range: 0 to 1.
DENSE_RANK() returns the rank of the current row within its partition, without gaps; peers (ties) receive the same rank, and the next rank after a tie is not skipped.
FIRST_VALUE(expr) returns the value of expr from the first row of the window frame.
LAG(expr [, N[, default]]) returns the value of expr from the row N rows before the current row within its partition (default N=1); returns default (default NULL) if there is no such row.
LAST_VALUE(expr) returns the value of expr from the last row of the window frame.
LEAD(expr [, N[, default]]) returns the value of expr from the row N rows after the current row within its partition, analogous to LAG().
NTH_VALUE(expr, N) returns the value of expr from the N-th row of the window frame, or NULL if there is no such row.
NTILE(N) divides a partition into N roughly equal buckets and returns the bucket number of the current row.
PERCENT_RANK() returns the row's relative rank as (rank-1)/(rows-1), excluding the highest value. Range: 0 to 1.
RANK() returns the rank of the current row within its partition, with gaps — ties share a rank, and the rank after a tie skips ahead by the tie's size.
ROW_NUMBER() returns the sequential number of the current row within its partition.

SELECT year, country, product, profit,
ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
FROM sales;

row_num1 numbers rows within each country's partition in whatever order they happen to be produced (undefined without an ORDER BY); row_num2 numbers them deterministically by year then product.

SELECT year, country, product, profit,
    ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
    ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
    FROM sales;

SELECT year, country, product, profit,
    ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
    ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
    FROM sales;

year	country	product	profit	row_num1	row_num2
2000	Finland	Computer	1500	2	1
2000	Finland	Phone	100	1	2
2001	Finland	Phone	10	3	3
2000	India	Calculator	75	2	1
2000	India	Calculator	75	3	2
2000	India	Computer	1200	1	3
2000	USA	Calculator	75	5	1
2000	USA	Computer	1500	4	2
2001	USA	Calculator	50	2	3
2001	USA	Computer	1500	3	4
2001	USA	Computer	1200	7	5
2001	USA	TV	150	1	6
2001	USA	TV	100	6	7

Named Windows

A window definition can be named with WINDOW and reused across multiple OVER clauses. The two statements below are equivalent:

SELECT val,
  ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  RANK() OVER (ORDER BY val) AS 'rank',
  DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;

SELECT val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK() OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

A named window can also be extended with additional clauses at the point of use:

SELECT DISTINCT year, country,
FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);

Practical Patterns

Deleting duplicate rows via a ranked CTE:

WITH CTE AS
(SELECT *, RANK() OVER (ORDER BY col1,col2,col3) AS R FROM MyTable)
DELETE FROM CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1);

Pagination with ROW_NUMBER():

SELECT * FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum, *
  FROM Orders
  WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1 AND RowNum < 20
ORDER BY RowNum;

-- Delete duplicate rows, keeping one copy per (col1,col2,col3) group
WITH CTE AS
(SELECT *, RANK() OVER (ORDER BY col1,col2,col3) AS R FROM MyTable)
DELETE FROM CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1);

-- Page through Orders 19 rows at a time using ROW_NUMBER()
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum, *
    FROM Orders
    WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1 AND RowNum < 20
ORDER BY RowNum;

-- Delete duplicate rows, keeping one copy per (col1,col2,col3) group
WITH CTE AS
(SELECT *, RANK() OVER (ORDER BY col1,col2,col3) AS R FROM MyTable)
DELETE FROM CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1);

-- Page through Orders 19 rows at a time using ROW_NUMBER()
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum, *
    FROM Orders
    WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1 AND RowNum < 20
ORDER BY RowNum;

Aggregate	<<<	Data Manipulation Language	>>>	Type Conversion
last update: 17 Aug 2026

MENU

Window Functions

Window-Only Functions

Named Windows

Practical Patterns