SQL Window Functions: ROW_NUMBER, RANK, PARTITION BY

A window function calculates a value from a set of related rows and writes the answer onto every one of those rows, instead of collapsing them into a single summary row. That one property lets a single query rank sales inside each region or keep a running total beside every sale, which plain aggregation cannot do without extra joins.

A bank statement is the everyday version of the idea. Each transaction keeps its own line, and the balance column beside it is worked out from that line plus every line above it.

A monthly summary replaces all of those lines with one total, which is what GROUP BY does. A window function is the balance column: the lines stay, and a calculated value appears next to each one.

Where PostgreSQL, MySQL, or SQL Server behave differently from SQLite, the text says so.

What Is a SQL Window Function?

A window function is a function followed by an OVER clause. The OVER clause describes the window, meaning the set of rows the function is allowed to look at when it calculates the value for the current row.

Each of the three terms below maps onto the bank statement:

• Partition: a group of rows that is calculated separately, like a separate statement for each account.
• Window order: the order of rows inside a partition, like the date order on the statement.
• Frame: the slice of the partition used for the current row, like "this line and every line above it" for a running balance.

Rows that share the same value in the window's ORDER BY are called peers. Peers matter for ties, and they come back in the ranking and frames sections.

The Sample Table

Every example uses this small sales table. It has two regions and a few deliberate ties, because ties are where window functions surprise people.

CREATE TABLE sales (
  id       INTEGER PRIMARY KEY,
  region   VARCHAR(10) NOT NULL,
  rep      VARCHAR(20) NOT NULL,
  sale_day DATE NOT NULL,
  amount   INTEGER NOT NULL
);

INSERT INTO sales VALUES
  (1, 'North', 'Ana',  '2026-03-02', 400),
  (2, 'North', 'Ben',  '2026-03-02', 250),
  (3, 'North', 'Ana',  '2026-03-03', 250),
  (4, 'North', 'Cara', '2026-03-05', 300),
  (5, 'South', 'Dev',  '2026-03-02', 500),
  (6, 'South', 'Eli',  '2026-03-04', 500),
  (7, 'South', 'Dev',  '2026-03-04', 150),
  (8, 'South', 'Fay',  '2026-03-06', 350);

The column types are plain enough for SQLite, PostgreSQL, MySQL, and SQL Server to accept the script unchanged. SQLite has no separate date type, so it stores sale_day as ISO text, which still sorts in date order.

Window Functions Compared with GROUP BY

Start with the monthly summary version of the question. GROUP BY returns one row per region, and the individual sales are gone from the result:

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

region	total
North	1200
South	1500

The window version keeps all eight sales and adds the total beside each one. That difference is the whole reason window functions exist, and the next section shows it.

How Does the OVER() Clause Work?

The general shape is a function, the keyword OVER, and up to three optional parts inside the parentheses:

function_name(args) OVER (
  [PARTITION BY expr, ...]
  [ORDER BY expr [ASC | DESC], ...]
  [frame_clause]
)

• PARTITION BY splits the rows into groups that are calculated independently. Leave it out and the whole result is one partition.
• ORDER BY sets the order inside each partition. Ranking functions and LAG/LEAD need it to mean anything.
• frame_clause narrows the rows used for the current row, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

The simplest possible window is an empty pair of parentheses. OVER () means "every row in the result", so an aggregate returns the same grand total on every line:

SELECT rep, amount,
       SUM(amount) OVER () AS all_sales
FROM sales
ORDER BY id;

rep	amount	all_sales
Ana	400	2700
Ben	250	2700
Ana	250	2700
Cara	300	2700
Dev	500	2700
Eli	500	2700
Dev	150	2700
Fay	350	2700

Eight rows went in and eight rows came out. The all_sales column is the balance column from the analogy, calculated here over the entire table.

PARTITION BY Splits the Rows into Groups

Add PARTITION BY region and the sum restarts for each region. This is the separate statement for each account:

SELECT region, rep, amount,
       SUM(amount) OVER (
         PARTITION BY region
       ) AS region_total
FROM sales
ORDER BY id;

region	rep	amount	region_total
North	Ana	400	1200
North	Ben	250	1200
North	Ana	250	1200
North	Cara	300	1200
South	Dev	500	1500
South	Eli	500	1500
South	Dev	150	1500
South	Fay	350	1500

The totals match the GROUP BY result above, 1200 and 1500. The difference is that each sale is still present, so a query can now compare a single sale with its region total in the same row.

ORDER BY Inside the Window

The ORDER BY inside OVER() is separate from the ORDER BY at the end of the query. The inner one decides how the window function sees the rows, and the outer one decides how the finished result is displayed.

Adding an inner ORDER BY also changes what an aggregate such as SUM returns.

Without it, the frame is the whole partition. With it, the default frame runs from the start of the partition to the current row and its peers, which turns SUM into a running total. The running-totals section below relies on exactly that behavior.

ROW_NUMBER vs RANK vs DENSE_RANK: How Ties Change the Numbers

The three ranking functions agree until two rows tie. The sample table has two ties, a pair of 500 sales and a pair of 250 sales, so ranking every sale by amount shows all three behaviors at once:

SELECT amount,
  ROW_NUMBER() OVER (
    ORDER BY amount DESC) AS row_num,
  RANK() OVER (
    ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (
    ORDER BY amount DESC) AS dense
FROM sales
ORDER BY amount DESC, row_num;

amount	row_num	rnk	dense
500	1	1	1
500	2	1	1
400	3	3	2
350	4	4	3
300	5	5	4
250	6	6	5
250	7	6	5
150	8	8	6

Read the table one column at a time:

• ROW_NUMBER() gives every row a different number, 1 to 8. The two 500 sales get 1 and 2, and which of them gets 1 is up to the engine.
• RANK() gives tied rows the same number and then skips. Both 500 sales rank 1, and the 400 sale ranks 3 because two rows are ahead of it.
• DENSE_RANK() gives tied rows the same number without skipping. The 400 sale ranks 2, because it holds the second-highest distinct amount.

The choice follows the question being asked. ROW_NUMBER suits "exactly one row per slot", RANK suits competition-style placings, and DENSE_RANK suits "the second-highest distinct value", which is a common interview question.

Running Totals with SUM() OVER

A running total is the bank-statement balance in its literal form. Partition by region, order by date, and SUM adds each sale to everything before it in that region:

SELECT region, sale_day, amount,
       SUM(amount) OVER (
         PARTITION BY region
         ORDER BY sale_day, id
       ) AS running
FROM sales
ORDER BY region, sale_day, id;

region	sale_day	amount	running
North	2026-03-02	400	400
North	2026-03-02	250	650
North	2026-03-03	250	900
North	2026-03-05	300	1200
South	2026-03-02	500	500
South	2026-03-04	500	1000
South	2026-03-04	150	1150
South	2026-03-06	350	1500

The last row of each region lands on the region total, 1200 and 1500, which is a quick way to check the query.

The id in the window's ORDER BY is there for a reason: two North sales share a date, and without a tiebreaker the default frame treats them as peers. The frames section shows what that does to the numbers.

Comparing Rows with LAG and LEAD

LAG reads a value from an earlier row in the partition, and LEAD reads one from a later row. Before window functions, the same comparison usually meant joining a table to itself on "the previous row", which is awkward to write and easy to get wrong.

For most of the years I ran MySQL on LAMP servers, that self-join was a common option, because MySQL did not add window functions until version 8.0 in 2018, and writing that self-join is one job I have been glad to give up.

SELECT region, amount,
       LAG(amount) OVER (
         PARTITION BY region
         ORDER BY sale_day, id
       ) AS prev_amt,
       LEAD(amount) OVER (
         PARTITION BY region
         ORDER BY sale_day, id
       ) AS next_amt
FROM sales
ORDER BY region, sale_day, id;

region	amount	prev_amt	next_amt
North	400	NULL	250
North	250	400	250
North	250	250	300
North	300	250	NULL
South	500	NULL	500
South	500	500	150
South	150	500	350
South	350	150	NULL

The first row of each region has no previous row, so LAG returns NULL, and the last row gets NULL from LEAD. The sqlite3 shell prints NULL as an empty cell by default; the table above spells it out.

Both functions take two optional arguments: how many rows to look back or ahead, and a default to use instead of NULL. A zero default makes the first row's change equal to its own amount, which is useful when the result feeds a chart:

SELECT sale_day, amount,
       amount - LAG(amount, 1, 0) OVER (
         ORDER BY sale_day, id
       ) AS change
FROM sales
WHERE region = 'North'
ORDER BY sale_day, id;

sale_day	amount	change
2026-03-02	400	400
2026-03-02	250	-150
2026-03-03	250	0
2026-03-05	300	50

The WHERE region = 'North' filter runs before the window function, so the window only ever sees North rows. That ordering of clauses is also the cause of the most common window-function error, covered in the pitfalls section.

Top-N per Group with ROW_NUMBER

"The two biggest sales in each region" is a common first reason to learn window functions. Number the rows inside each region, then keep the rows numbered 1 and 2. The numbering has to happen in an inner query, because a window function cannot be filtered in the same query's WHERE clause:

WITH ranked AS (
  SELECT region, rep, amount,
         ROW_NUMBER() OVER (
           PARTITION BY region
           ORDER BY amount DESC, id
         ) AS pos
  FROM sales
)
SELECT region, rep, amount, pos
FROM ranked
WHERE pos <= 2
ORDER BY region, pos;

region	rep	amount	pos
North	Ana	400	1
North	Cara	300	2
South	Dev	500	1
South	Eli	500	2

The WITH ranked AS (...) part is a common table expression, a named subquery that the outer query reads like a table. A plain subquery in the FROM clause works the same way if you prefer it.

Look closely at the South rows in that result. Dev and Eli both sold 500, and ROW_NUMBER still had to put one of them first.

The id tiebreaker makes that choice repeatable. When tied rows should all be kept, switch to RANK:

WITH ranked AS (
  SELECT region, rep, amount,
         RANK() OVER (
           PARTITION BY region
           ORDER BY amount DESC
         ) AS pos
  FROM sales
)
SELECT region, rep, amount
FROM ranked
WHERE pos = 1
ORDER BY region, rep;

region	rep	amount
North	Ana	400
South	Dev	500
South	Eli	500

"Top sale per region" now returns three rows, because South has a genuine tie for first place. My rule is to settle which answer the report needs before choosing the function, since both queries are correct for different questions.

Window Frames: ROWS vs RANGE

The frame is the part of the partition that an aggregate uses for the current row. ROWS counts physical rows, one at a time, back from the current row. RANGE works on the values in the window's ORDER BY, so rows with equal values (peers) enter or leave the frame together, unless an EXCLUDE option says otherwise.

When a window has an ORDER BY and no frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default is the reason two sales on the same day can show the same running total. This query orders only by date, then compares the default frame with a ROWS frame:

SELECT sale_day, amount,
       SUM(amount) OVER (
         ORDER BY sale_day
       ) AS by_range,
       SUM(amount) OVER (
         ORDER BY sale_day
         ROWS UNBOUNDED PRECEDING
       ) AS by_rows
FROM sales
WHERE region = 'North'
ORDER BY sale_day, id;

sale_day	amount	by_range	by_rows
2026-03-02	400	650	400
2026-03-02	250	650	650
2026-03-03	250	900	900
2026-03-05	300	1200	1200

Both 2 March sales show 650 under by_range, because the frame includes every peer of the current row. The by_rows column counts rows one at a time instead.

However, the order of the two tied rows inside that frame is still the engine's choice, so another engine could put 250 before 400. Adding a unique tiebreaker such as id to the window's ORDER BY fixes both problems.

Frames also make it possible to calculate moving values such as a rolling average. This query averages each South sale with up to two sales before it:

SELECT sale_day, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY sale_day, id
         ROWS BETWEEN 2 PRECEDING
                  AND CURRENT ROW
       ), 1) AS avg_last_3
FROM sales
WHERE region = 'South'
ORDER BY sale_day, id;

sale_day	amount	avg_last_3
2026-03-02	500	500.0
2026-03-04	500	500.0
2026-03-04	150	383.3
2026-03-06	350	333.3

The first two rows average fewer than three values because there is nothing further back. SQL Server returns an integer when AVG is applied to an integer column, so there the argument needs a cast, such as AVG(amount * 1.0), to keep the decimals.

How Do PostgreSQL, MySQL, SQLite, and SQL Server Differ?

The core functions on this page work in all four engines on any reasonably current release. The differences are mostly in which versions added support and how far frame syntax goes:

Engine	Window Functions From	Frame Limits
PostgreSQL	8.4	ROWS, RANGE, GROUPS, EXCLUDE
MySQL	8.0	ROWS and RANGE only
SQLite	3.25.0	GROUPS and EXCLUDE from 3.28.0
SQL Server	2012 for this page	RANGE without numeric offsets

The details behind those cells, engine by engine:

• PostgreSQL added window functions in 8.4. Every currently supported release accepts ROWS, RANGE, and GROUPS frames, plus the EXCLUDE option. SQLite's developers used the PostgreSQL documentation as their primary reference and test against PostgreSQL, so the two rarely disagree.
• MySQL added window functions in 8.0. Its parser recognizes GROUPS frames and EXCLUDE but raises an error for both, and it does not support DISTINCT inside an aggregate window function, as in COUNT(DISTINCT rep) OVER (...).
• SQLite added window functions in 3.25.0 and extended frames in 3.28.0. Python's sqlite3 module uses whichever SQLite library it was built against, so check with python3 -c "import sqlite3; print(sqlite3.sqlite_version)" before relying on it.
• SQL Server has had the ranking functions for longer, but frames, running totals with ORDER BY, and LAG/LEAD all need SQL Server 2012 or later. ROW_NUMBER requires an ORDER BY in its window, and RANGE frames accept only UNBOUNDED and CURRENT ROW boundaries.

Two smaller differences change results rather than syntax. The first is NULL ordering: PostgreSQL sorts NULL after other values in ascending order, while MySQL, SQLite, and SQL Server sort it first, which moves NULL rows to the top or bottom of a ranking. PostgreSQL and SQLite accept NULLS FIRST and NULLS LAST to make that explicit.

The second is the named WINDOW clause, which defines a window once and reuses it by name, as in RANK() OVER w ... WINDOW w AS (ORDER BY amount DESC).

PostgreSQL, MySQL, and SQLite all support the named WINDOW clause. SQL Server supports it from SQL Server 2022, and only when the database compatibility level is 160 or higher. The examples on this page repeat the window inline so they run on older SQL Server releases and lower compatibility levels too.

For a wider comparison of the three open-source engines, see MySQL vs PostgreSQL vs SQLite.

Common Pitfalls and Debugging Tips

Many window-function bugs trace back to one of two misunderstandings: when the window is calculated relative to the rest of the query, and what the default frame contains. The errors below follow from one or the other.

Filtering on a Window Function in WHERE

Filtering on a window function in the same query fails, because WHERE runs before any window function has been calculated:

SELECT region, rep, amount
FROM sales
WHERE ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
      ) = 1;

SQLite reports misuse of window function ROW_NUMBER(), and PostgreSQL reports window functions are not allowed in WHERE. Moving the window into the SELECT list and filtering on its alias fails too: SQLite reports misuse of aliased window function pos, and PostgreSQL reports column "pos" does not exist, which is the more confusing message of the two.

The fix in every engine is the pattern from the top-N section. Calculate the window in a subquery or common table expression, then filter on its alias in the outer query. The same restriction applies to GROUP BY and HAVING, since window functions are only allowed in the SELECT list and the final ORDER BY.

LAST_VALUE Returns the Current Row

FIRST_VALUE behaves as expected with the default frame, because the frame always starts at the first row. LAST_VALUE does not, because the default frame ends at the current row, or at its last peer when the window's ORDER BY has ties:

SELECT sale_day, amount,
       LAST_VALUE(amount) OVER (
         ORDER BY sale_day, id
       ) AS last_amt
FROM sales
WHERE region = 'North'
ORDER BY sale_day, id;

sale_day	amount	last_amt
2026-03-02	400	400
2026-03-02	250	250
2026-03-03	250	250
2026-03-05	300	300

Each row reports its own amount, which looks like the function is broken. The query ran correctly and the frame was simply too short. Extend the frame to the end of the partition and every row reports 300, the last North sale:

SELECT sale_day, amount,
       LAST_VALUE(amount) OVER (
         ORDER BY sale_day, id
         ROWS BETWEEN UNBOUNDED PRECEDING
              AND UNBOUNDED FOLLOWING
       ) AS last_amt
FROM sales
WHERE region = 'North'
ORDER BY sale_day, id;

An alternative that needs no frame at all is FIRST_VALUE(amount) OVER (ORDER BY sale_day DESC, id DESC), which reverses the order and takes the first row.

Mixing GROUP BY with a Window Function

When a query has both GROUP BY and a window function, the grouping happens first and the window runs over the grouped rows. A window that refers to an ungrouped column is therefore an error in PostgreSQL, SQL Server, and MySQL's default ONLY_FULL_GROUP_BY mode:

SELECT region,
       SUM(amount) OVER () AS grand
FROM sales
GROUP BY region;

PostgreSQL refuses to run this query and reports the error column "sales.amount" must appear in the GROUP BY clause or be used in an aggregate function. SQLite accepts it, because SQLite allows ungrouped "bare" columns and takes their value from an arbitrary row in each group.

On the sample table it can return 900 on both rows, a number that matches neither region's total nor the grand total of 2700. I would rather have PostgreSQL's error, because a query that runs and returns a wrong number deserves more suspicion than one that fails.

The correct form wraps the grouped aggregate inside the window aggregate. The inner SUM produces one total per region, and the outer SUM ... OVER () adds those totals up:

SELECT region,
       SUM(amount) AS total,
       SUM(SUM(amount)) OVER () AS grand,
       ROUND(100.0 * SUM(amount)
         / SUM(SUM(amount)) OVER (), 1)
         AS pct
FROM sales
GROUP BY region
ORDER BY region;

region	total	grand	pct
North	1200	2700	44.4
South	1500	2700	55.6

The 100.0 literal in that query matters as well. Dividing one integer by another truncates the result in SQLite, PostgreSQL, and SQL Server, and the decimal literal forces a decimal division.

ROW_NUMBER Without a Tiebreaker

When the window's ORDER BY has ties, ROW_NUMBER still has to number the tied rows, and the order it picks is not guaranteed. The query can return different numbers after an index change, an engine upgrade, or a different execution plan, with no error at all.

The symptom is a report that changes between runs even though the data did not. Add a unique column, usually the primary key, as the last item in the window's ORDER BY. Pagination code built on ROW_NUMBER needs the same fix, or rows can repeat or vanish between pages.

Best Practices and Trade-offs

Window functions sit beside the tools a reader probably knows already. This table shows where each one fits:

Approach	Keeps Each Row	Best For
GROUP BY	No, one row per group	Totals and counts per group
Window function	Yes	Ranks, running totals, row comparisons
Self-join or subquery	Yes	Engines without window support

A few habits keep window queries correct and readable:

• End every window ORDER BY with a unique column whenever the order must be repeatable.
• Write the frame out explicitly for LAST_VALUE, moving averages, and any running total over data that can tie.
• Filter on a window result in an outer query, and give the inner query a name that says what it ranks.
• Check the plan with EXPLAIN on large tables. Each distinct window usually means a sort, and an index that matches the PARTITION BY and ORDER BY columns, such as (region, sale_day), can let the planner avoid one.

The SQL indexes and query optimization guide explains how to read that plan. For totals that genuinely need one row per group, GROUP BY is still the simpler and clearer query.

Conclusion

A window function is the balance column on the bank statement: every row stays in the result, and a value calculated from its neighbors sits beside it. PARTITION BY decides which statement a row belongs to, the window's ORDER BY decides the order of the lines, and the frame decides how far back the calculation looks.

For most reporting work, I would learn ROW_NUMBER, RANK, SUM() OVER, and LAG first, and add a tiebreaker whenever the order has to be repeatable. The default frame and the "filter in an outer query" rule sit behind every pitfall on this page, so check those two first when a result looks wrong.

Frequently Asked Questions

What is the difference between a window function and GROUP BY?

GROUP BY collapses each group into a single output row, so the individual rows disappear from the result. A window function calculates across the same kind of group but keeps every input row, adding the calculated value as an extra column beside the original data.

Can you use a window function in a WHERE clause?

Not directly, because window functions are calculated after WHERE, GROUP BY, and HAVING have already run, so the value does not exist yet when WHERE filters rows. Calculate the window function in a subquery or common table expression, then filter on its alias in the outer query.

What happens when OVER() has no PARTITION BY clause?

The whole result set becomes one partition. With an empty OVER (), every row sees every row, so SUM(amount) OVER () returns the grand total on each row. With only an ORDER BY, ROW_NUMBER() numbers the entire result from 1 upward without restarting.

When should you use DENSE_RANK instead of RANK?

Use DENSE_RANK when you want to ask for the top distinct values, such as the three highest amounts, regardless of how many rows share each value. RANK leaves gaps after ties, which matches how competitions usually number places after a shared result.

What does LAG return when there is no previous row?

LAG returns NULL for the first row of each partition, because there is nothing before it. Pass a third argument to supply a default instead, as in LAG(amount, 1, 0), which returns 0 on the first row. LEAD behaves the same way at the end of a partition.

Does every database support SQL window functions?

The major relational engines do on any reasonably current release. MySQL needs version 8.0 or later, SQLite needs 3.25.0 or later, SQL Server has the running-total and LAG features from 2012, and PostgreSQL has supported window functions since 8.4. Frame options vary more than the functions do.

Are window functions slow on large tables?

They usually cost a sort for each distinct PARTITION BY and ORDER BY combination in the query. On a large table that sort is the main expense. An index that matches those columns can let the planner skip it, so check the plan with EXPLAIN before rewriting anything.

Self-Check

1. Three rows have amounts 90, 90, and 80, ordered by amount descending. What does RANK() return for the 80 row, and what does DENSE_RANK() return?
2. What does SUM(amount) OVER () return on each row of the sample table?
3. Why does WHERE ROW_NUMBER() OVER (ORDER BY amount) = 1 fail?
4. A running total ordered only by sale_day shows the same value on two rows. What causes that, and how do you fix it?
5. Which function would you use to show each sale next to the previous sale in the same region?
6. Why does LAST_VALUE(amount) OVER (ORDER BY sale_day, id) return each row's own amount?

Answers

1. RANK() returns 3 for the 80 row, because two rows rank ahead of it and the rank skips 2. DENSE_RANK() returns 2, because it never leaves gaps after a tie.
2. It returns 2700 on every row, the grand total of all eight sales, because an empty window treats the whole result as one partition.
3. The WHERE clause runs before window functions are calculated, so the value does not exist yet. Calculate it in a subquery or CTE and filter in the outer query.
4. The two rows share a date, so they are peers, and the default RANGE frame includes both of them. Add a unique tiebreaker such as id to the window's ORDER BY, or use a ROWS frame with that tiebreaker.
5. Use LAG(amount) OVER (PARTITION BY region ORDER BY sale_day, id), which returns NULL on the first sale of each region.
6. The default frame ends at the current row, and this window's ORDER BY is unique, so the last row of the frame is the current row. Extend the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Next Steps

Window functions build directly on joins and aggregation, and they are a regular topic in intermediate SQL interviews. The pages below pick up where this guide leaves off:

• Common SQL interview questions, including the "second-highest salary" question that DENSE_RANK answers cleanly.
• SQL joins, the older way to compare rows with related rows, and still the right tool for combining tables.
• SQL UNION and UNION ALL, the other query technique in this section, for stacking result sets.
• MySQL vs PostgreSQL vs SQLite, for the engine differences beyond window functions.
• The engine guides for PostgreSQL, MySQL, and SQLite, or the SQL guide for the full topic list.