The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →A SQL window function calculates a value from a set of related rows and attaches that value to each row in the result, so you keep the individual rows while still getting group-level or sequence-level context. If you want to show every employee next to their department’s average salary, or each month’s sales next to the previous month’s, a window function does it in one query without collapsing the detail.
The DEV Community tutorial this article follows, “SQL Is Surviving, Franklin: Now Rows Are Competing” by Faith Njenga, frames the lesson around a teaching character named Franklin. Its sample lines are illustrative, not quotations from a real expert. The tutorial uses generic SQL. The examples below are checked against the PostgreSQL 18 documentation, and the differences that matter for other engines are flagged where they arise.
As an Amazon Associate I earn from qualifying purchases.
How a window function differs from GROUP BY
The core difference is output grain. A GROUP BY query reduces many input rows to one row per group. A window function reads the same group or sequence of rows but returns one row for every input row. PostgreSQL’s window functions tutorial describes the idea directly: a window function performs a calculation across a set of table rows that are somehow related to the current row.
| Feature | GROUP BY aggregate | Window function |
|---|---|---|
| Output rows | One row per group | One row per input row |
| Individual row columns | Not available after grouping, unless they are also grouped | Kept alongside the calculated value |
| Where it can appear | GROUP BY and HAVING filter groups | SELECT and ORDER BY in PostgreSQL 18; not WHERE |
| Department average example | Engineering 87500; Sales 68333.33 (two rows) | Each of the five employees with their department’s average (five rows) |
Keep every row and add group context
Consider a small employees table. The values are illustrative sample data used throughout this article.
#1 Best Overall
| employee | department | salary |
|---|---|---|
| Dev | Engineering | 90000 |
| Eli | Engineering | 85000 |
| Ana | Sales | 70000 |
| Ben | Sales | 70000 |
| Cara | Sales | 65000 |
SELECT
employee,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS department_avg
FROM employees;This is the pattern the DEV Community tutorial uses for its opening question: show every employee, their salary, and the average salary of their department. The query returns five rows, not two.
| employee | department | salary | department_avg |
|---|---|---|---|
| Dev | Engineering | 90000 | 87500.00 |
| Eli | Engineering | 85000 | 87500.00 |
| Ana | Sales | 70000 | 68333.33 |
| Ben | Sales | 70000 | 68333.33 |
| Cara | Sales | 65000 | 68333.33 |
Averages are shown rounded to two decimals for readability. The exact value of the Sales average is 205000 divided by 3; the database returns more decimal places depending on the data type of the expression.
Anatomy of the OVER clause
Every window function call has an OVER clause. It defines which rows the function sees for each output row. The tutorial’s SQL can be read in three parts, and a fourth part, the frame, appears when order matters.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteOVER: the window specification
The keyword OVER turns an ordinary function into a window function. An empty window, written OVER (), treats the entire result set as one window. For example, AVG(salary) OVER () attaches the company-wide average to every row.
PARTITION BY: the calculation groups
PARTITION BY splits the rows into independent groups. Each group is calculated separately, and rows in one group never affect another group’s result. Omitting PARTITION BY means all rows form a single partition. In the employee example, PARTITION BY department produces two partitions: Engineering with two rows and Sales with three.
ORDER BY inside OVER: order within the window
ORDER BY inside OVER does not sort the final result. It defines the sequence the window function uses for ranking, previous or next values, and running totals. You can use an ORDER BY in the OVER clause and a different ORDER BY at the end of the query.
Frames: which rows inside the window count
A frame limits which rows in the current partition contribute to calculations such as SUM, AVG, or FIRST_VALUE. Frames matter for running and moving calculations and are covered in their own section below.
Ranking functions and ties
ROW_NUMBER, RANK, and DENSE_RANK all assign a position based on the window ORDER BY, but they treat equal values differently. The DEV Community tutorial introduces these three; the behavior described here follows the PostgreSQL window functions reference, which documents RANK and DENSE_RANK tie handling for the function reference rather than the tutorial’s examples.
SELECT
employee,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, employee) AS row_number,
RANK() OVER (ORDER BY salary DESC) AS salary_rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM employees;| employee | salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Dev | 90000 | 1 | 1 | 1 |
| Eli | 85000 | 2 | 2 | 2 |
| Ana | 70000 | 3 | 3 | 3 |
| Ben | 70000 | 4 | 3 | 3 |
| Cara | 65000 | 5 | 5 | 4 |
Ana and Ben are peers because their ORDER BY values are equal. RANK gives both the value 3 and then skips to 5 for Cara. DENSE_RANK gives both 3 and continues with 4. ROW_NUMBER gives every row a distinct position, so Ana and Ben receive 3 and 4 only because the tie-breaker employee places Ana first.
- Use ROW_NUMBER when you need one row per position, such as picking one representative row per group, and add a unique tie-breaker so the choice is repeatable.
- Use RANK when equal values should share a position and gaps after ties are meaningful, as in competition standings.
- Use DENSE_RANK when you need consecutive positions with no gaps, such as a list of distinct salary levels.
LAG and LEAD: reading neighboring rows
LAG returns a value from a preceding row within the ordered partition, and LEAD returns a value from a following row. The PostgreSQL function reference defaults the offset to one row and returns NULL when no such row exists. You can pass an explicit offset and a fallback value as the third argument.
SELECT
month,
sales,
LAG(sales) OVER (ORDER BY month) AS previous_month_sales,
sales - LAG(sales) OVER (ORDER BY month) AS change_from_previous
FROM monthly_sales;| month | sales | previous_month_sales | change_from_previous |
|---|---|---|---|
| Jan | 100 | NULL | NULL |
| Feb | 120 | 100 | 20 |
| Mar | 90 | 120 | -30 |
| Apr | 150 | 90 | 60 |
The NULL in the first row is expected, not an error. To replace it with zero, write LAG(sales, 1, 0). To compare within groups, add a PARTITION BY, for example LAG(sales) OVER (PARTITION BY region ORDER BY month), which stops the comparison at each region boundary.
Recommended Free Tools
Running totals and moving averages
Running totals and moving averages depend on the frame. The two queries below use the same monthly sales as the LAG example and show the results side by side.
The default frame includes peers
When an ORDER BY is present and no frame is given, PostgreSQL uses RANGE from the start of the partition through the current row’s last peer. Peers are rows whose ORDER BY values are equal. The consequence is that tied rows can share one cumulative result. If two rows both have the month value Jan, with amounts 100 and 50, a default running sum shows 150 on both rows, not 100 and then 150.
An explicit ROWS frame for a row-by-row running total
SELECT
month,
sales,
SUM(sales) OVER (
ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM monthly_sales;ROWS counts physical rows rather than value ranges, so the result advances one row at a time. If month is not unique, add a stable tie-breaker to the ORDER BY so the running total is deterministic.
A trailing three-row average
SELECT
month,
sales,
AVG(sales) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS trailing_three_rows_avg
FROM monthly_sales;| month | sales | running_total (ROWS) | trailing_three_rows_avg |
|---|---|---|---|
| Jan | 100 | 100 | 100.00 |
| Feb | 120 | 220 | 110.00 |
| Mar | 90 | 310 | 103.33 |
| Apr | 150 | 460 | 120.00 |
This frame counts three rows, not three calendar months. If a month is missing from the table, the “trailing three” average silently spans a longer period. If you need a true time interval, you must express it with date or range logic supported by your database, and check that engine’s RANGE behavior before relying on it.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsFiltering on a window result
A window function cannot be used in a WHERE clause of the same SELECT. In PostgreSQL, the window is computed after WHERE is applied, so the calculated value is not yet available there. The fix is to compute the window in a common table expression or subquery, then filter in the outer query.
WITH ranked AS (
SELECT
employee,
department,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees
)
SELECT *
FROM ranked
WHERE salary_rank = 1;With the sample data, this returns Dev (Engineering), and both Ana and Ben (Sales). RANK gives Ana and Ben the same position, so both are kept. If you intended exactly one top earner per department, use ROW_NUMBER with a tie-breaker in the same pattern.
Combining GROUP BY with window functions
Window functions run after ordinary grouping and aggregation, so you can rank grouped results. The following query totals salaries per department and ranks those totals:
SELECT
department,
SUM(salary) AS total_salary,
RANK() OVER (ORDER BY SUM(salary) DESC) AS department_rank
FROM employees
GROUP BY department;Sales totals 205000 and ranks 1; Engineering totals 175000 and ranks 2. Here the GROUP BY is necessary because the query reports one row per department. The window function operates on those department rows. The PostgreSQL expressions reference states that window functions are evaluated after ordinary aggregates.
Database-specific details to check
The examples above are checked against PostgreSQL 18 documentation. Other engines support window functions, but the details vary, so do not assume identical behavior. Check these points in your own database:
- Default frame. PostgreSQL’s default with ORDER BY is RANGE from the partition start through the current row’s last peer. Explicit ROWS or other frame clauses change this.
- Supported frame modes and syntax. Frame clauses and their options are not uniform across engines.
- NULL handling in LAG and LEAD. The PostgreSQL function reference states that its implementation always uses RESPECT NULLS for these functions, so the NULL-skipping option is not available there.
- Window in WHERE. The rule that window calls belong in SELECT and ORDER BY is a PostgreSQL 18 rule as documented; use the CTE pattern shown above wherever it applies.
Troubleshooting checklist
- Error about a window function in WHERE. Move the calculation into a CTE or subquery and filter in the outer query.
- Ranks skip numbers after a tie. That is RANK’s documented behavior. Switch to DENSE_RANK if you need consecutive positions.
- Running total repeats the same value for several rows. The default RANGE frame includes peers with equal ORDER BY values. Use ROWS and add a unique tie-breaker.
- Row numbers change between runs. ROW_NUMBER is not deterministic when the ORDER BY has ties. Add a unique column, such as an ID, to the ORDER BY.
- First LAG value is NULL. That is expected for the first row in a partition. Supply a default as the third argument if your reports need a value.
- Moving average looks wrong across missing months. ROWS counts rows, so a missing month shifts the window. Use a date-aware approach if the requirement is calendar based.
The principle to remember
Window functions answer questions that ask for a row’s value in relation to other rows, while keeping the rows themselves. GROUP BY answers questions that ask for one row per group. Choosing between them comes down to whether the output should keep its input rows, which is the question the title’s “now rows are competing” points to.
Quick Recap
The Bottom Line
“”
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
