GROUP BY does. They let you rank rows, compute running totals, and compare a row with its neighbors. In this chapter, you will learn how to use ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), and SUM() OVER() with the OVER and PARTITION BY clauses.What Are SQL Window Functions?
A window function performs a calculation across a “window” of rows defined by the OVER() clause. Unlike aggregate functions used with GROUP BY, window functions do not collapse rows — every row in the result keeps its identity while also receiving the computed value.
Think of it like this: GROUP BY gives you one summary row per group, but a window function gives you the summary alongside every original row. This is incredibly useful for rankings, running totals, and row-to-row comparisons.
Window Function Syntax
function_name(...) OVER (
PARTITION BY column_name
ORDER BY column_name
)Here is what each part does:
| Part | Purpose | Required? |
|---|---|---|
| function_name() | The window function — ROW_NUMBER, RANK, SUM, etc. |
Yes |
| OVER() | Defines the window of rows the function operates on | Yes |
| PARTITION BY | Splits rows into groups (like GROUP BY, but without collapsing) | Optional |
| ORDER BY | Defines the order of rows inside each partition | Depends on function |
Sample Data
We will use the Employees table throughout this chapter. If you have been following along from our SQL SELECT chapter, this table will look familiar:
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+ROW_NUMBER() — Assign a Unique Row Number
ROW_NUMBER() assigns a sequential integer to each row within a partition, starting at 1. Every row gets a unique number — no ties.
SELECT Name, Department, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employees;+-----------+------------+--------+--------+ | Name | Department | Salary | RowNum | +-----------+------------+--------+--------+ | Sara L. | HR | 58000 | 1 | | Arun M. | IT | 78000 | 1 | | Ravi S. | IT | 72000 | 2 | | John D. | Marketing | 62000 | 1 | | Priya K. | Sales | 55000 | 1 | | Emma W. | Sales | 51000 | 2 | +-----------+------------+--------+--------+
Notice how ROW_NUMBER() restarts at 1 for each department because we used PARTITION BY Department. Rows are ordered by Salary DESC, so the highest earner in each group gets number 1.
RANK() and DENSE_RANK() — Handle Ties in Rankings
When two rows have the same value, ROW_NUMBER() still assigns different numbers. If you want ties to share the same rank, use RANK() or DENSE_RANK().
| Function | What It Does | After a Tie |
|---|---|---|
| ROW_NUMBER() | Unique number — no ties | Continues sequentially |
| RANK() | Same rank for ties | Skips next number (1, 1, 3) |
| DENSE_RANK() | Same rank for ties | Does NOT skip (1, 1, 2) |
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS RankNum,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;+-----------+--------+---------+--------------+ | Name | Salary | RankNum | DenseRankNum | +-----------+--------+---------+--------------+ | Arun M. | 78000 | 1 | 1 | | Ravi S. | 72000 | 2 | 2 | | John D. | 62000 | 3 | 3 | | Sara L. | 58000 | 4 | 4 | | Priya K. | 55000 | 5 | 5 | | Emma W. | 51000 | 6 | 6 | +-----------+--------+---------+--------------+
In this data there are no ties, so both columns look identical. If two employees shared the same salary, RANK() would skip a number after the tie while DENSE_RANK() would not. Understanding this difference is essential when building leaderboards or paginated reports.
LEAD() and LAG() — Access Previous and Next Rows
LAG() looks at a previous row and LEAD() looks at the next row, based on the ORDER BY inside the OVER clause. They are perfect for calculating differences between consecutive rows — such as month-over-month changes.
SELECT Name, Salary,
LAG(Salary) OVER (ORDER BY Salary) AS PrevSalary,
LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;+-----------+--------+------------+------------+ | Name | Salary | PrevSalary | NextSalary | +-----------+--------+------------+------------+ | Emma W. | 51000 | NULL | 55000 | | Priya K. | 55000 | 51000 | 58000 | | Sara L. | 58000 | 55000 | 62000 | | John D. | 62000 | 58000 | 72000 | | Ravi S. | 72000 | 62000 | 78000 | | Arun M. | 78000 | 72000 | NULL | +-----------+--------+------------+------------+
The first row has NULL for PrevSalary because there is no row before it. Similarly, the last row has NULL for NextSalary. You can provide a default value: LAG(Salary, 1, 0) returns 0 instead of NULL. To learn more about handling NULLs, revisit Chapter 9: SQL NULL Values.
SUM() OVER — Running Totals
Aggregate functions like SUM(), AVG(), and COUNT() can also be used as window functions. When you add an OVER() clause, they calculate a running aggregate instead of a single total. You already explored these aggregates in Chapter 14: SQL COUNT, AVG, SUM — now you will see them in a window context.
SELECT Name, Department, Salary,
SUM(Salary) OVER (ORDER BY ID) AS RunningTotal
FROM Employees;+-----------+------------+--------+--------------+ | Name | Department | Salary | RunningTotal | +-----------+------------+--------+--------------+ | Priya K. | Sales | 55000 | 55000 | | John D. | Marketing | 62000 | 117000 | | Arun M. | IT | 78000 | 195000 | | Emma W. | Sales | 51000 | 246000 | | Ravi S. | IT | 72000 | 318000 | | Sara L. | HR | 58000 | 376000 | +-----------+------------+--------+--------------+
Each row shows the cumulative salary total up to that row. This technique is widely used in financial reports, dashboards, and KPI scorecards.
PARTITION BY with Aggregates — Group-Level Calculations
Combine PARTITION BY with an aggregate window function to compute group-level values alongside every row. This is one of the most powerful patterns in SQL window functions.
SELECT Name, Department, Salary,
SUM(Salary) OVER (PARTITION BY Department) AS DeptTotal,
AVG(Salary) OVER (PARTITION BY Department) AS DeptAvg,
COUNT(*) OVER (PARTITION BY Department) AS DeptCount
FROM Employees;+-----------+------------+--------+-----------+---------+-----------+ | Name | Department | Salary | DeptTotal | DeptAvg | DeptCount | +-----------+------------+--------+-----------+---------+-----------+ | Sara L. | HR | 58000 | 58000 | 58000 | 1 | | Arun M. | IT | 78000 | 150000 | 75000 | 2 | | Ravi S. | IT | 72000 | 150000 | 75000 | 2 | | John D. | Marketing | 62000 | 62000 | 62000 | 1 | | Priya K. | Sales | 55000 | 106000 | 53000 | 2 | | Emma W. | Sales | 51000 | 106000 | 53000 | 2 | +-----------+------------+--------+-----------+---------+-----------+
Every row retains its individual detail while also showing the department-level totals, averages, and counts. This is something you cannot achieve with GROUP BY alone — you would need a subquery or a JOIN back to the aggregated data.
💡 Window Functions vs GROUP BY
GROUP BY reduces rows — you get one row per group. Window functions keep all rows and add the computed value as a new column. Use GROUP BY when you only need the summary. Use window functions when you need both the detail and the summary in the same result.
⚠️ Compatibility Note
Window functions are supported in MySQL 8.0+, PostgreSQL, SQL Server 2012+, SQLite 3.25+, and Oracle. If you are using an older MySQL version (5.x), window functions will not work. Check the official MySQL window function documentation for details.
If you are building data dashboards, SQL window functions are a game-changer. Check out our E-commerce KPI Scorecard on Neotech Navigators to see how ranked and aggregated data powers real-world dashboards.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query that shows each employee’s name, department, salary, and their rank within their department (highest salary = rank 1). Also show the salary difference compared to the next-lower-paid employee in the same department. Use RANK() and LAG().
SELECT Name, Department, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank,
Salary - LAG(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryDiff
FROM Employees;Expected Output:
+-----------+------------+--------+----------+------------+ | Name | Department | Salary | DeptRank | SalaryDiff | +-----------+------------+--------+----------+------------+ | Sara L. | HR | 58000 | 1 | NULL | | Arun M. | IT | 78000 | 1 | NULL | | Ravi S. | IT | 72000 | 2 | -6000 | | John D. | Marketing | 62000 | 1 | NULL | | Priya K. | Sales | 55000 | 1 | NULL | | Emma W. | Sales | 51000 | 2 | -4000 | +-----------+------------+--------+----------+------------+
Great work! You just combined two window functions — RANK() and LAG() — in a single query. This is a common pattern in analytics and reporting.
📝 What You Learned in This Chapter
OVER()— turns an aggregate or ranking function into a window functionPARTITION BY— splits the result into groups without collapsing rowsROW_NUMBER()— assigns a unique sequential number to each rowRANK()— ranks rows with gaps after tiesDENSE_RANK()— ranks rows with no gaps after tiesLAG()andLEAD()— access previous and next row valuesSUM() OVER()— creates running totals and partition-level aggregates- Window functions keep all rows;
GROUP BYcollapses them
Frequently Asked Questions
OVER() clause. Unlike GROUP BY aggregates, they do not collapse the result set — every original row is preserved in the output alongside the computed value.RANK() assigns the same rank to tied rows but skips subsequent numbers. For example, two rows at rank 1 means the next rank is 3. DENSE_RANK() also assigns tied rows the same rank but does not skip — the next rank would be 2. Use DENSE_RANK() when you need a continuous sequence.PARTITION BY divides the result set into groups (partitions) so the window function is applied independently within each group. It works like GROUP BY but keeps all the individual rows intact. For example, PARTITION BY Department computes separate rankings or totals for each department.WHERE clause. To filter by a window function result (for example, only rows where rank = 1), wrap the query in a subquery or CTE and apply the filter in the outer query. This technique is covered in Chapter 28: SQL Subqueries.LAG() returns a value from a previous row in the result set, while LEAD() returns a value from the next row. Both accept an optional offset (default 1) and a default value for when no row exists. They are commonly used for calculating period-over-period changes in financial and sales reports.📖 Chapter 29 of 30 — SQL Tutorial on Neotech Navigators
