SQL Server Window Functions Guide with Examples

A report has to show every row of a table beside a value computed from a group of related rows: a running total, a position inside the group, the value one row back. GROUP BY cannot produce it, because grouping collapses the rows it aggregates into one. The OVER clause can. It hands a function a window of rows to read, and the query still returns one row per input row.

The examples run on SQL Server 2022 against one table:

CREATE TABLE employees (
    emp_id    int PRIMARY KEY,
    dept_id   int NOT NULL,
    salary    decimal(9, 2) NOT NULL,
    hire_date date NOT NULL
);

INSERT INTO employees VALUES
    (1, 10, 4000, '2020-01-10'),
    (2, 10, 6000, '2020-06-20'),
    (3, 20, 5000, '2020-02-15'),
    (4, 20, 7000, '2020-08-30'),
    (5, 30, 5500, '2020-03-25'),
    (6, 30, 6500, '2020-10-05'),
    (7, 10, 6000, '2021-02-01');

What a window function keeps that GROUP BY throws away

The department total beside each employee is one window function and no subquery:

SELECT emp_id, dept_id, salary,
       SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
FROM employees
ORDER BY dept_id, emp_id;

PARTITION BY divides the result set into partitions, and the computation restarts for each one. Drop it and the whole result set is a single partition, so SUM(salary) OVER () puts the company total of 40000.00 on all seven rows. The OVER clause documentation states both defaults, and it also warns that the expression in PARTITION BY can only refer to columns from the FROM clause, never to an alias you defined in the select list.

The same functions with a PostgreSQL flavor, and the reason they show up in feature engineering, are in window functions in SQL. What follows stays with T-SQL.

Aggregate functions over a moving frame

Add ORDER BY inside OVER and the window becomes a frame that grows as SQL Server walks the partition. The frame in this query starts at the first row of the partition and ends at the current row, which is what turns a total into a running total:

SELECT emp_id, dept_id, hire_date, salary,
       SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees
ORDER BY dept_id, hire_date;

ROWS counts a fixed number of rows around the current row, RANGE takes every row whose ORDER BY value equals the current one, and either form requires the ORDER BY. Leave the frame out and the documented default applies: with an ORDER BY in the OVER clause, functions that accept a frame use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Write the frame anyway when you mean rows, because the two defaults differ on ties: ROWS ... CURRENT ROW stops at the current row, RANGE ... CURRENT ROW includes every row that shares its ordering value.

RANK, DENSE_RANK, ROW_NUMBER and NTILE inside each department

T-SQL has four ranking functions, and the interesting difference between them shows up on a tie. Employees 2 and 7 both earn 6000.00:

SELECT emp_id, dept_id, salary,
       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_dense_rank
FROM employees
ORDER BY dept_id, salary DESC, emp_id;

RANK skips the numbers it used on the tie, so the third employee in department 10 is ranked 3. DENSE_RANK gives out consecutive numbers and ranks that employee 2. ROW_NUMBER never repeats a number, which is why it needs a tiebreaker in the ORDER BY: the documentation says the numbering is only repeatable when the values of the ordering columns are unique. Adding emp_id makes it so:

SELECT emp_id, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS salary_row
FROM employees
ORDER BY dept_id, salary_row;

The fourth ranking function, NTILE, splits each partition into the number of groups you ask for and labels every row with its group number. All four are nondeterministic, and none of them accepts a ROWS or RANGE frame.

LAG and LEAD read the row before and the row after

Comparing a row with its neighbor used to mean joining the table to itself. LAG and LEAD fetch the value directly, one query, one pass:

SELECT emp_id, dept_id, hire_date, salary,
       LAG(salary)  OVER (PARTITION BY dept_id ORDER BY hire_date) AS prev_salary,
       LEAD(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS next_salary
FROM employees
ORDER BY dept_id, hire_date;

The first row of each partition has nothing behind it and the last has nothing ahead of it, so those cells are NULL. Both functions take an optional default value as a third argument if you would rather see a zero there. The analytic functions family they belong to also holds FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK, PERCENTILE_CONT and PERCENTILE_DISC.

What an OVER clause refuses

Four restrictions are worth carrying in your head. OVER cannot be combined with a DISTINCT aggregation. RANGE accepts UNBOUNDED and CURRENT ROW but not a row count, so RANGE BETWEEN 2 PRECEDING AND CURRENT ROW is rejected and the same frame with ROWS is accepted. Ranking functions take no frame at all. And PARTITION BY cannot reference a select-list alias, because the select list is bound after the clauses that precede it.

The window function itself is an expression in the select list, so a WHERE clause cannot see its result. To keep only the top two earners per department, compute the number in a common table expression and filter around it, the way the ROW_NUMBER documentation does:

WITH ranked AS (
    SELECT emp_id, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS salary_row
    FROM employees
)
SELECT emp_id, dept_id, salary
FROM ranked
WHERE salary_row <= 2
ORDER BY dept_id, salary_row;

No special privilege comes with any of this. Reading the rows requires SELECT permission on the table or view, inherited from the schema or from membership in the db_datareader fixed database role, exactly as for a query with no window function.

Running the queries in sqlcmd

Connect with the server, the database and the login, and leave the password off the command line:

sqlcmd -S localhost -d demo -U sa

The sqlcmd documentation is direct about why: passing the password with -P is insecure, and omitting it makes sqlcmd prompt you instead. Once connected, paste a query, then type GO on a line of its own to send the batch. sqlcmd prints the result as fixed-width text columns, which is readable for seven rows and hard work for a wide report.

The same queries in the DbSchema SQL Editor

DbSchema shows the same result as a grid you can sort and scroll. Choose Connect to Database, pick SQL Server, fill in the server host, port, database and credentials, and click Connect. DbSchema reverse-engineers the schema and draws the tables as a diagram, so you can read the column names of employees while you write the OVER clause.

Open the SQL Editor from the Editors menu, paste one of the queries above, and click Execute Query. It runs the statement at the cursor and shows the rows in the result pane. The query reaches the connected SQL Server database directly; the editor text is saved in the DbSchema model file, so the same window function is one click away tomorrow.

Window functions are the shortest route from a row to the group around it, and they cost nothing to try: connecting, reverse-engineering the schema, the diagram and the SQL Editor are all in the free Community Edition. Download DbSchema at https://dbschema.com/download.html, connect to your SQL Server database, and run the running-total query against a table of your own.

Sources

1. OVER Clause (Transact-SQL)
2. Ranking Functions (Transact-SQL)
3. Analytic Functions (Transact-SQL)
4. ROW_NUMBER (Transact-SQL)
5. SELECT (Transact-SQL)
6. sqlcmd utility
7. DbSchema SQL Editor