intro
Window functions are one of the most powerful tools in the modern SQL world, yet many developers default to GROUP BY for every aggregation problem. This article covers some of the SQL window patterns that solve the majority of real-world problems and each pattern works across PostgreSQL, MySQL 8+, and SQL Server.
Introduction
You have a sales table and you need to rank each rep within their region. GROUP BY collapses rows into groups; window functions compute across a group while leaving every row intact.
The difference is in the output. GROUP BY collapses to one row per rep per region:
| rep_id | region | total |
|---|---|---|
| 3 | East | 5200.00 |
| 7 | East | 4800.00 |
A window function returns the same rows with a new column added:
| rep_id | region | total | rank_in_region |
|---|---|---|---|
| 3 | East | 5200.00 | 1 |
| 7 | East | 4800.00 | 2 |
That second result comes from adding ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) to the query. The OVER clause is what makes it a window function rather than an aggregate.
How Do SQL Window Functions Work?
Every window function follows the same pattern:
1
function_name() OVER (
2
PARTITION BY col1
3
ORDER BY col2
4
)
PARTITION BY divides the result set into groups without collapsing rows. ORDER BY controls the row sequence within each group. The OVER clause is what makes the function a window function; PARTITION BY and ORDER BY inside it are both optional. Leave out PARTITION BY and the function runs across the entire result set. Leave out ORDER BY and the function treats the whole partition as one unordered set, computing the same value for every row in it.
Window functions are available in PostgreSQL 8.4+, MySQL 8.0+, and SQL Server 2012+. The syntax is standard SQL and mostly portable across all three.
One rule to know before writing your first query is that you cannot use a window function in a WHERE or HAVING clause. If you need to filter on a window function result, wrap the query in a CTE or subquery. The deduplication section below shows this pattern.
Running totals
The most common first use is a cumulative sum that resets by group.
1
SELECT
2
sale_id,
3
rep_id,
4
region,
5
amount,
6
SUM(amount) OVER (
7
PARTITION BY region
8
ORDER BY sale_date
9
) AS regional_running_total
10
FROM sales;
Sample output:
| sale_id | rep_id | region | amount | regional_running_total |
|---|---|---|---|---|
| 1 | 3 | East | 450.00 | 450.00 |
| 4 | 7 | East | 320.00 | 770.00 |
| 8 | 3 | East | 510.00 | 1280.00 |
| 2 | 1 | West | 600.00 | 600.00 |
| 5 | 2 | West | 410.00 | 1010.00 |
The sum accumulates within each region and resets when the partition changes. Removing ORDER BY changes the behavior completely. With SUM(amount) OVER (PARTITION BY region) and no ORDER BY, every row based in the East (region) gets the same value:
| sale_id | region | amount | regional_running_total |
|---|---|---|---|
| 1 | East | 450.00 | 1280.00 |
| 4 | East | 320.00 | 1280.00 |
| 8 | East | 510.00 | 1280.00 |
That is the region total repeated on every row. Useful for computing each row's share of its group total, but not a running sum.
You can write and iterate on these queries in a SQL editor like DbVisualizer, which lets you sort and filter the result set in place without re-running the query.
Ranking within a group
As far as relational databases are concerned, three functions handle ranking - ROW_NUMBER(), RANK(), and DENSE_RANK():
1
SELECT
2
rep_id,
3
region,
4
SUM(amount) AS total,
5
ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS row_num,
6
RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rank_position,
7
DENSE_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS dense_rank_position
8
FROM sales
9
GROUP BY rep_id, region;
The results are as follows:
| rep_id | region | total | row_num | rank_position | dense_rank_position |
|---|---|---|---|---|---|
| 3 | East | 5200.00 | 1 | 1 | 1 |
| 7 | East | 4800.00 | 2 | 2 | 2 |
| 9 | East | 4800.00 | 3 | 2 | 2 |
| 5 | East | 4100.00 | 4 | 4 | 3 |
Here, ROW_NUMBER() assigns a unique consecutive number to every row, RANK() assigns the same number to tied rows but leaves gaps in the sequence that are allocated for the ties, and DENSE_RANK() provides an uninterrupted, continuous sequence.
You see this here:
Use ROW_NUMBER() when you need exactly one winner per group. Use RANK() or DENSE_RANK() when the tied position is meaningful data.
Deduplication
As far as deduplication goes, you will mostly use ROW_NUMBER().
ROW_NUMBER() is the standard approach for removing duplicate rows because you can preview exactly what the delete will remove before running it. If your table has duplicates with the same rep, date, and amount but different sale_id values, start with the SELECT:
1
WITH ranked AS (
2
SELECT
3
*,
4
ROW_NUMBER() OVER (
5
PARTITION BY rep_id, sale_date, amount
6
ORDER BY sale_id
7
) AS rn
8
FROM sales
9
)
10
SELECT * FROM ranked WHERE rn > 1;
Sample output. These are the rows that would be deleted:
| sale_id | rep_id | sale_date | amount | rn |
|---|---|---|---|---|
| 8 | 3 | 2026-01-15 | 450.00 | 2 |
| 12 | 3 | 2026-01-15 | 450.00 | 3 |
| 19 | 7 | 2026-02-03 | 320.00 | 2 |
PARTITION BY groups rows that should be identical. ORDER BY sale_id keeps the lowest ID (earliest insert). Once the SELECT output looks correct, run the delete:
1
-- PostgreSQL and SQL Server
2
WITH ranked AS (
3
SELECT
4
sale_id,
5
ROW_NUMBER() OVER (
6
PARTITION BY rep_id, sale_date, amount
7
ORDER BY sale_id
8
) AS rn
9
FROM sales
10
)
11
DELETE FROM sales
12
WHERE sale_id IN (SELECT sale_id FROM ranked WHERE rn > 1);
In MySQL 8+, DELETE does not support CTEs directly, so you would use a join as a subquery instead:
1
-- MySQL 8+
2
DELETE s
3
FROM sales s
4
JOIN (
5
SELECT sale_id,
6
ROW_NUMBER() OVER (
7
PARTITION BY rep_id, sale_date, amount
8
ORDER BY sale_id
9
) AS rn
10
FROM sales
11
) ranked ON s.sale_id = ranked.sale_id
12
WHERE ranked.rn > 1;
Both versions work the same way: the window function runs inside a subquery or CTE, and the outer DELETE filters on the result. This is the pattern you use whenever you need to filter on a window function.
Performance Considerations
A window function over a large unindexed table would sort the entire partition: to avoid this, you can put the PARTITION BY columns first in a composite index, then the ORDER BY columns afterwards. For the running total query above, this would look like so:
1
CREATE INDEX idx_sales_region_date ON sales (region, sale_date);
region comes first because it is the partition key. sale_date comes second because it is the sort key within each partition. An index that reverses this order (sale_date, region) will not help the window sort.
You can confirm the index is being used by checking the explain plan. DbVisualizer’s explain plan viewer displays the query plan graphically. Look for a sort node. If it shows a disk sort, the partition is too large for memory and an index is missing or the index order is wrong.
Practical Use Cases
You would use SQL window functions if you deal with:
Frequently Asked Questions
Can I use a window function in a WHERE clause?
No. Window functions are computed after WHERE, so the column doesn't exist yet at filter time. Wrap the query in a CTE or subquery and filter on the result in the outer SELECT. The deduplication example above shows the pattern.
What database management systems support window functions?
PostgreSQL added window functions in the version 8.4 released in 2009, SQL Server added window functions in 2012, and MySQL added window functions starting with MySQL 8.0 (2018). SQLite added window functions with the version 3.25 which was released in 2018. Oracle has had them since the version 8i.
What to learn next
To learn more about functions in relational database management systems, keep yourself up to date by visiting blogs like TheTable by DbVisualizer, reading books on MySQL and other database management systems, as well as attending conferences and workshops on databases and related things. You will get the grasp fairly quickly!
If you don’t want to get started on either of those, grab a free trial of DbVisualizer and get your hands dirty running some SQL window functions.
