Window Functions in SQL Server — Sample Data, Queries and Results

A window function calculates a value using a group of related rows while keeping every individual row in the output.

The OVER() clause identifies it as a window function.

1. Create sample table and data

CREATE TABLE Employees
(
    EmployeeId   INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Department   VARCHAR(20),
    Salary       DECIMAL(10,2),
    JoiningDate  DATE
);

INSERT INTO Employees
(
    EmployeeId,
    EmployeeName,
    Department,
    Salary,
    JoiningDate
)
VALUES
(1, 'Arun',   'IT',      60000, '2021-01-10'),
(2, 'Bala',   'IT',      50000, '2022-03-15'),
(3, 'Farooq', 'IT',      70000, '2020-07-01'),
(4, 'Ganesh', 'IT',      60000, '2023-05-20'),
(5, 'Chitra', 'HR',      45000, '2021-06-12'),
(6, 'Divya',  'HR',      40000, '2022-08-18'),
(7, 'Hema',   'HR',      45000, '2020-11-25'),
(8, 'Imran',  'Finance', 55000, '2021-04-10'),
(9, 'John',   'Finance', 65000, '2019-09-05');

Sample data

SELECT *
FROM Employees
ORDER BY EmployeeId;

Result:

EmployeeId	EmployeeName	Department	Salary	JoiningDate
1	Arun	IT	60,000	2021-01-10
2	Bala	IT	50,000	2022-03-15
3	Farooq	IT	70,000	2020-07-01
4	Ganesh	IT	60,000	2023-05-20
5	Chitra	HR	45,000	2021-06-12
6	Divya	HR	40,000	2022-08-18
7	Hema	HR	45,000	2020-11-25
8	Imran	Finance	55,000	2021-04-10
9	John	Finance	65,000	2019-09-05

2. Aggregate function with `GROUP BY`

First, understand how normal aggregation works.

SELECT
    Department,
    SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Result:

Department	TotalSalary
Finance	120,000
HR	130,000
IT	240,000

The nine employee records are reduced to three department records. Employee details are not available.

3. Aggregate window function

SELECT
    EmployeeName,
    Department,
    Salary,
    SUM(Salary) OVER
    (
        PARTITION BY Department
    ) AS DepartmentTotal
FROM Employees
ORDER BY Department, EmployeeId;

Result:

EmployeeName	Department	Salary	DepartmentTotal
Imran	Finance	55,000	120,000
John	Finance	65,000	120,000
Chitra	HR	45,000	130,000
Divya	HR	40,000	130,000
Hema	HR	45,000	130,000
Arun	IT	60,000	240,000
Bala	IT	50,000	240,000
Farooq	IT	70,000	240,000
Ganesh	IT	60,000	240,000

Here, every employee remains visible, and the department total is displayed beside every employee.

4. Understanding `OVER()`

The basic syntax is:

WindowFunction()
OVER
(
    PARTITION BY column
    ORDER BY column
    ROWS BETWEEN ...
)

The OVER() clause can contain three important parts:

SUM(Salary) OVER
(
    PARTITION BY Department
    ORDER BY EmployeeId
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Part	Purpose
`PARTITION BY`	Divides rows into groups
`ORDER BY`	Determines the calculation order
`ROWS` or `RANGE`	Defines which rows participate

Not every window function requires all three parts.

5. `SUM()` — total salary by department

SELECT
    EmployeeName,
    Department,
    Salary,
    SUM(Salary) OVER
    (
        PARTITION BY Department
    ) AS DepartmentTotal
FROM Employees
ORDER BY Department, EmployeeId;

Result:

EmployeeName	Department	Salary	DepartmentTotal
Imran	Finance	55,000	120,000
John	Finance	65,000	120,000
Chitra	HR	45,000	130,000
Divya	HR	40,000	130,000
Hema	HR	45,000	130,000
Arun	IT	60,000	240,000
Bala	IT	50,000	240,000
Farooq	IT	70,000	240,000
Ganesh	IT	60,000	240,000

6. `AVG()` — average salary by department

SELECT
    EmployeeName,
    Department,
    Salary,
    AVG(Salary) OVER
    (
        PARTITION BY Department
    ) AS DepartmentAverage
FROM Employees
ORDER BY Department, EmployeeId;

Result:

EmployeeName	Department	Salary	DepartmentAverage
Imran	Finance	55,000	60,000.00
John	Finance	65,000	60,000.00
Chitra	HR	45,000	43,333.33
Divya	HR	40,000	43,333.33
Hema	HR	45,000	43,333.33
Arun	IT	60,000	60,000.00
Bala	IT	50,000	60,000.00
Farooq	IT	70,000	60,000.00
Ganesh	IT	60,000	60,000.00

7. Multiple aggregate window functions

SELECT
    EmployeeName,
    Department,
    Salary,

    COUNT(*) OVER
    (
        PARTITION BY Department
    ) AS EmployeeCount,

    MIN(Salary) OVER
    (
        PARTITION BY Department
    ) AS MinimumSalary,

    MAX(Salary) OVER
    (
        PARTITION BY Department
    ) AS MaximumSalary,

    AVG(Salary) OVER
    (
        PARTITION BY Department
    ) AS AverageSalary

FROM Employees
ORDER BY Department, EmployeeId;

Result:

EmployeeName	Department	Salary	EmployeeCount	MinimumSalary	MaximumSalary	AverageSalary
Imran	Finance	55,000	2	55,000	65,000	60,000.00
John	Finance	65,000	2	55,000	65,000	60,000.00
Chitra	HR	45,000	3	40,000	45,000	43,333.33
Divya	HR	40,000	3	40,000	45,000	43,333.33
Hema	HR	45,000	3	40,000	45,000	43,333.33
Arun	IT	60,000	4	50,000	70,000	60,000.00
Bala	IT	50,000	4	50,000	70,000	60,000.00
Farooq	IT	70,000	4	50,000	70,000	60,000.00
Ganesh	IT	60,000	4	50,000	70,000	60,000.00

8. `ROW_NUMBER()`

ROW_NUMBER() assigns a unique sequential number to every row.

SELECT
    EmployeeName,
    Department,
    Salary,
    ROW_NUMBER() OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS RowNumber
FROM Employees
ORDER BY Department, RowNumber;

Result:

EmployeeName	Department	Salary	RowNumber
John	Finance	65,000	1
Imran	Finance	55,000	2
Chitra	HR	45,000	1
Hema	HR	45,000	2
Divya	HR	40,000	3
Farooq	IT	70,000	1
Arun	IT	60,000	2
Ganesh	IT	60,000	3
Bala	IT	50,000	4

Even when two employees have the same salary, they receive different row numbers.

Because the salaries may be equal, add a tie-breaker to make the result predictable:

ROW_NUMBER() OVER
(
    PARTITION BY Department
    ORDER BY Salary DESC, EmployeeId
)

9. `RANK()`

RANK() gives the same rank to equal values. The next rank is skipped after a tie.

SELECT
    EmployeeName,
    Department,
    Salary,
    RANK() OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS SalaryRank
FROM Employees
ORDER BY Department, SalaryRank, EmployeeId;

Result:

EmployeeName	Department	Salary	SalaryRank
John	Finance	65,000	1
Imran	Finance	55,000	2
Chitra	HR	45,000	1
Hema	HR	45,000	1
Divya	HR	40,000	3
Farooq	IT	70,000	1
Arun	IT	60,000	2
Ganesh	IT	60,000	2
Bala	IT	50,000	4

In IT:

70000 → Rank 1
60000 → Rank 2
60000 → Rank 2
50000 → Rank 4

Rank 3 is skipped.

10. `DENSE_RANK()`

DENSE_RANK() also gives the same rank to equal values, but it does not skip the next rank.

SELECT
    EmployeeName,
    Department,
    Salary,
    DENSE_RANK() OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS SalaryRank
FROM Employees
ORDER BY Department, SalaryRank, EmployeeId;

Result:

EmployeeName	Department	Salary	SalaryRank
John	Finance	65,000	1
Imran	Finance	55,000	2
Chitra	HR	45,000	1
Hema	HR	45,000	1
Divya	HR	40,000	2
Farooq	IT	70,000	1
Arun	IT	60,000	2
Ganesh	IT	60,000	2
Bala	IT	50,000	3

In IT:

70000 → Rank 1
60000 → Rank 2
60000 → Rank 2
50000 → Rank 3

11. Difference between ranking functions

For easy comparison, examine only the IT department:

SELECT
    EmployeeName,
    Salary,

    ROW_NUMBER() OVER
    (
        ORDER BY Salary DESC, EmployeeId
    ) AS RowNumber,

    RANK() OVER
    (
        ORDER BY Salary DESC
    ) AS RankNumber,

    DENSE_RANK() OVER
    (
        ORDER BY Salary DESC
    ) AS DenseRankNumber

FROM Employees
WHERE Department = 'IT'
ORDER BY Salary DESC, EmployeeId;

Result:

EmployeeName	Salary	RowNumber	RankNumber	DenseRankNumber
Farooq	70,000	1	1	1
Arun	60,000	2	2	2
Ganesh	60,000	3	2	2
Bala	50,000	4	4	3

Function	Duplicate values	Gaps after ties
`ROW_NUMBER()`	Different numbers	Not applicable
`RANK()`	Same rank	Yes
`DENSE_RANK()`	Same rank	No

12. Find the highest-paid employee in each department

Use ROW_NUMBER() inside a CTE:

WITH RankedEmployees AS
(
    SELECT
        EmployeeId,
        EmployeeName,
        Department,
        Salary,
        ROW_NUMBER() OVER
        (
            PARTITION BY Department
            ORDER BY Salary DESC, EmployeeId
        ) AS RowNumber
    FROM Employees
)
SELECT
    EmployeeName,
    Department,
    Salary
FROM RankedEmployees
WHERE RowNumber = 1
ORDER BY Department;

Result:

EmployeeName	Department	Salary
John	Finance	65,000
Chitra	HR	45,000
Farooq	IT	70,000

For HR, Chitra and Hema have the same salary. However, ROW_NUMBER() returns only one employee because EmployeeId is used as the tie-breaker.

13. Find all highest-paid employees, including ties

Use DENSE_RANK() or RANK():

WITH RankedEmployees AS
(
    SELECT
        EmployeeName,
        Department,
        Salary,
        DENSE_RANK() OVER
        (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS SalaryRank
    FROM Employees
)
SELECT
    EmployeeName,
    Department,
    Salary
FROM RankedEmployees
WHERE SalaryRank = 1
ORDER BY Department, EmployeeName;

Result:

EmployeeName	Department	Salary
John	Finance	65,000
Chitra	HR	45,000
Hema	HR	45,000
Farooq	IT	70,000

Both Chitra and Hema are returned because they share the highest HR salary.

14. Find the top two salaries in each department

WITH RankedEmployees AS
(
    SELECT
        EmployeeName,
        Department,
        Salary,
        DENSE_RANK() OVER
        (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS SalaryRank
    FROM Employees
)
SELECT
    EmployeeName,
    Department,
    Salary,
    SalaryRank
FROM RankedEmployees
WHERE SalaryRank <= 2
ORDER BY Department, SalaryRank, EmployeeName;

Result:

EmployeeName	Department	Salary	SalaryRank
John	Finance	65,000	1
Imran	Finance	55,000	2
Chitra	HR	45,000	1
Hema	HR	45,000	1
Divya	HR	40,000	2
Farooq	IT	70,000	1
Arun	IT	60,000	2
Ganesh	IT	60,000	2

DENSE_RANK() returns all employees whose salaries belong to the top two salary levels.

15. `NTILE()`

NTILE(n) divides the result into approximately equal groups.

SELECT
    EmployeeName,
    Salary,
    NTILE(3) OVER
    (
        ORDER BY Salary DESC
    ) AS SalaryGroup
FROM Employees
ORDER BY Salary DESC, EmployeeId;

Result:

EmployeeName	Salary	SalaryGroup
Farooq	70,000	1
John	65,000	1
Arun	60,000	1
Ganesh	60,000	2
Imran	55,000	2
Bala	50,000	2
Chitra	45,000	3
Hema	45,000	3
Divya	40,000	3

The nine employees are divided into three groups:

Group 1: Higher salaries
Group 2: Middle salaries
Group 3: Lower salaries

Rows with equal values can fall into different groups because NTILE() attempts to keep group sizes balanced.

16. Running total

A running total adds the current value to all preceding values.

SELECT
    EmployeeId,
    EmployeeName,
    Salary,
    SUM(Salary) OVER
    (
        ORDER BY EmployeeId
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS RunningTotal
FROM Employees
ORDER BY EmployeeId;

Result:

EmployeeId	EmployeeName	Salary	RunningTotal
1	Arun	60,000	60,000
2	Bala	50,000	110,000
3	Farooq	70,000	180,000
4	Ganesh	60,000	240,000
5	Chitra	45,000	285,000
6	Divya	40,000	325,000
7	Hema	45,000	370,000
8	Imran	55,000	425,000
9	John	65,000	490,000

Meaning:

Row 1 = 60000
Row 2 = 60000 + 50000
Row 3 = 60000 + 50000 + 70000

17. Running total within each department

SELECT
    EmployeeName,
    Department,
    JoiningDate,
    Salary,
    SUM(Salary) OVER
    (
        PARTITION BY Department
        ORDER BY JoiningDate, EmployeeId
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS DepartmentRunningTotal
FROM Employees
ORDER BY Department, JoiningDate, EmployeeId;

Result:

EmployeeName	Department	JoiningDate	Salary	DepartmentRunningTotal
John	Finance	2019-09-05	65,000	65,000
Imran	Finance	2021-04-10	55,000	120,000
Hema	HR	2020-11-25	45,000	45,000
Chitra	HR	2021-06-12	45,000	90,000
Divya	HR	2022-08-18	40,000	130,000
Farooq	IT	2020-07-01	70,000	70,000
Arun	IT	2021-01-10	60,000	130,000
Bala	IT	2022-03-15	50,000	180,000
Ganesh	IT	2023-05-20	60,000	240,000

The running total restarts whenever the department changes.

18. Percentage contribution to department salary

SELECT
    EmployeeName,
    Department,
    Salary,
    SUM(Salary) OVER
    (
        PARTITION BY Department
    ) AS DepartmentTotal,

    CAST
    (
        Salary * 100.0 /
        SUM(Salary) OVER (PARTITION BY Department)
        AS DECIMAL(6,2)
    ) AS SalaryPercentage

FROM Employees
ORDER BY Department, EmployeeId;

Result:

EmployeeName	Department	Salary	DepartmentTotal	SalaryPercentage
Imran	Finance	55,000	120,000	45.83
John	Finance	65,000	120,000	54.17
Chitra	HR	45,000	130,000	34.62
Divya	HR	40,000	130,000	30.77
Hema	HR	45,000	130,000	34.62
Arun	IT	60,000	240,000	25.00
Bala	IT	50,000	240,000	20.83
Farooq	IT	70,000	240,000	29.17
Ganesh	IT	60,000	240,000	25.00

19. Compare salary with department average

SELECT
    EmployeeName,
    Department,
    Salary,
    AVG(Salary) OVER
    (
        PARTITION BY Department
    ) AS DepartmentAverage,

    Salary -
    AVG(Salary) OVER
    (
        PARTITION BY Department
    ) AS DifferenceFromAverage

FROM Employees
ORDER BY Department, EmployeeId;

Result:

EmployeeName	Department	Salary	DepartmentAverage	DifferenceFromAverage
Imran	Finance	55,000	60,000.00	-5,000.00
John	Finance	65,000	60,000.00	5,000.00
Chitra	HR	45,000	43,333.33	1,666.67
Divya	HR	40,000	43,333.33	-3,333.33
Hema	HR	45,000	43,333.33	1,666.67
Arun	IT	60,000	60,000.00	0.00
Bala	IT	50,000	60,000.00	-10,000.00
Farooq	IT	70,000	60,000.00	10,000.00
Ganesh	IT	60,000	60,000.00	0.00

A positive value means the employee earns above the department average. A negative value means the employee earns below it.

20. `LAG()` — access the previous row

LAG() retrieves a value from a preceding row.

SELECT
    EmployeeName,
    JoiningDate,
    Salary,
    LAG(Salary) OVER
    (
        ORDER BY JoiningDate
    ) AS PreviousEmployeeSalary
FROM Employees
ORDER BY JoiningDate;

Result:

EmployeeName	JoiningDate	Salary	PreviousEmployeeSalary
John	2019-09-05	65,000	`NULL`
Farooq	2020-07-01	70,000	65,000
Hema	2020-11-25	45,000	70,000
Arun	2021-01-10	60,000	45,000
Imran	2021-04-10	55,000	60,000
Chitra	2021-06-12	45,000	55,000
Bala	2022-03-15	50,000	45,000
Divya	2022-08-18	40,000	50,000
Ganesh	2023-05-20	60,000	40,000

The first row returns NULL because no previous row exists.

21. Compare with the previous employee’s salary

SELECT
    EmployeeName,
    JoiningDate,
    Salary,

    LAG(Salary) OVER
    (
        ORDER BY JoiningDate
    ) AS PreviousSalary,

    Salary -
    LAG(Salary) OVER
    (
        ORDER BY JoiningDate
    ) AS SalaryDifference

FROM Employees
ORDER BY JoiningDate;

Result:

EmployeeName	Salary	PreviousSalary	SalaryDifference
John	65,000	`NULL`	`NULL`
Farooq	70,000	65,000	5,000
Hema	45,000	70,000	-25,000
Arun	60,000	45,000	15,000
Imran	55,000	60,000	-5,000
Chitra	45,000	55,000	-10,000
Bala	50,000	45,000	5,000
Divya	40,000	50,000	-10,000
Ganesh	60,000	40,000	20,000

22. `LEAD()` — access the next row

LEAD() retrieves a value from the following row.

SELECT
    EmployeeName,
    JoiningDate,
    Salary,
    LEAD(Salary) OVER
    (
        ORDER BY JoiningDate
    ) AS NextEmployeeSalary
FROM Employees
ORDER BY JoiningDate;

Result:

EmployeeName	JoiningDate	Salary	NextEmployeeSalary
John	2019-09-05	65,000	70,000
Farooq	2020-07-01	70,000	45,000
Hema	2020-11-25	45,000	60,000
Arun	2021-01-10	60,000	55,000
Imran	2021-04-10	55,000	45,000
Chitra	2021-06-12	45,000	50,000
Bala	2022-03-15	50,000	40,000
Divya	2022-08-18	40,000	60,000
Ganesh	2023-05-20	60,000	`NULL`

The last row returns NULL because no following row exists.

23. `LAG()` within each department

SELECT
    EmployeeName,
    Department,
    JoiningDate,
    Salary,
    LAG(Salary) OVER
    (
        PARTITION BY Department
        ORDER BY JoiningDate
    ) AS PreviousDepartmentEmployeeSalary
FROM Employees
ORDER BY Department, JoiningDate;

Result:

EmployeeName	Department	JoiningDate	Salary	Previous Salary
John	Finance	2019-09-05	65,000	`NULL`
Imran	Finance	2021-04-10	55,000	65,000
Hema	HR	2020-11-25	45,000	`NULL`
Chitra	HR	2021-06-12	45,000	45,000
Divya	HR	2022-08-18	40,000	45,000
Farooq	IT	2020-07-01	70,000	`NULL`
Arun	IT	2021-01-10	60,000	70,000
Bala	IT	2022-03-15	50,000	60,000
Ganesh	IT	2023-05-20	60,000	50,000

The first employee in every department receives NULL because each department forms a separate window.

24. `FIRST_VALUE()`

FIRST_VALUE() returns the first value in an ordered window.

SELECT
    EmployeeName,
    Department,
    Salary,
    FIRST_VALUE(EmployeeName) OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC, EmployeeId
    ) AS HighestPaidEmployee
FROM Employees
ORDER BY Department, Salary DESC;

Result:

EmployeeName	Department	Salary	HighestPaidEmployee
John	Finance	65,000	John
Imran	Finance	55,000	John
Chitra	HR	45,000	Chitra
Hema	HR	45,000	Chitra
Divya	HR	40,000	Chitra
Farooq	IT	70,000	Farooq
Arun	IT	60,000	Farooq
Ganesh	IT	60,000	Farooq
Bala	IT	50,000	Farooq

The highest-paid employee’s name appears on every row of that department.

25. `LAST_VALUE()`

LAST_VALUE() returns the last value in the defined frame.

SELECT
    EmployeeName,
    Department,
    Salary,
    LAST_VALUE(EmployeeName) OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC, EmployeeId
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS LowestPaidEmployee
FROM Employees
ORDER BY Department, Salary DESC, EmployeeId;

Result:

EmployeeName	Department	Salary	LowestPaidEmployee
John	Finance	65,000	Imran
Imran	Finance	55,000	Imran
Chitra	HR	45,000	Divya
Hema	HR	45,000	Divya
Divya	HR	40,000	Divya
Farooq	IT	70,000	Bala
Arun	IT	60,000	Bala
Ganesh	IT	60,000	Bala
Bala	IT	50,000	Bala

The frame is important:

ROWS BETWEEN UNBOUNDED PRECEDING
         AND UNBOUNDED FOLLOWING

It instructs SQL Server to examine the complete partition—from the first row to the last row.

26. Moving average

Create monthly sales data:

CREATE TABLE MonthlySales
(
    MonthNumber INT PRIMARY KEY,
    MonthName   VARCHAR(15),
    SalesAmount DECIMAL(10,2)
);

INSERT INTO MonthlySales
VALUES
(1, 'January',  10000),
(2, 'February', 12000),
(3, 'March',    14000),
(4, 'April',    18000),
(5, 'May',      16000),
(6, 'June',     20000);

Calculate the average of the current month and previous two months:

SELECT
    MonthName,
    SalesAmount,
    AVG(SalesAmount) OVER
    (
        ORDER BY MonthNumber
        ROWS BETWEEN 2 PRECEDING
                 AND CURRENT ROW
    ) AS ThreeMonthMovingAverage
FROM MonthlySales
ORDER BY MonthNumber;

Result:

MonthName	SalesAmount	ThreeMonthMovingAverage
January	10,000	10,000.00
February	12,000	11,000.00
March	14,000	12,000.00
April	18,000	14,666.67
May	16,000	16,000.00
June	20,000	18,000.00

Calculation for April:

February + March + April
= 12000 + 14000 + 18000
= 44000 / 3
= 14666.67

27. Window frame options

Common frame boundaries are:

Frame boundary	Meaning
`UNBOUNDED PRECEDING`	First row in the partition
`n PRECEDING`	n rows before the current row
`CURRENT ROW`	Current row
`n FOLLOWING`	n rows after the current row
`UNBOUNDED FOLLOWING`	Last row in the partition

Current and previous row

SUM(SalesAmount) OVER
(
    ORDER BY MonthNumber
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)

Previous, current and next row

AVG(SalesAmount) OVER
(
    ORDER BY MonthNumber
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

From the first row to the current row

SUM(SalesAmount) OVER
(
    ORDER BY MonthNumber
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Entire partition

SUM(SalesAmount) OVER
(
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
)

28. `ROWS` versus `RANGE`

This difference becomes important when duplicate ordering values exist.

Using `ROWS`

ROWS works with physical row positions.

SELECT
    EmployeeName,
    Salary,
    SUM(Salary) OVER
    (
        ORDER BY Salary
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS RunningTotal
FROM Employees
WHERE Department = 'IT'
ORDER BY Salary, EmployeeId;

Possible result:

EmployeeName	Salary	RunningTotal
Bala	50,000	50,000
Arun	60,000	110,000
Ganesh	60,000	170,000
Farooq	70,000	240,000

Each physical row is added individually.

Using `RANGE`

RANGE treats rows with the same ordering value as peers.

SELECT
    EmployeeName,
    Salary,
    SUM(Salary) OVER
    (
        ORDER BY Salary
        RANGE BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW
    ) AS RunningTotal
FROM Employees
WHERE Department = 'IT'
ORDER BY Salary, EmployeeId;

Result:

EmployeeName	Salary	RunningTotal
Bala	50,000	50,000
Arun	60,000	170,000
Ganesh	60,000	170,000
Farooq	70,000	240,000

Both employees earning 60,000 are included together.

For predictable row-by-row running totals, explicitly use:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

29. Why can’t a window function be used directly in `WHERE`?

This query is invalid:

SELECT
    EmployeeName,
    Department,
    Salary,
    ROW_NUMBER() OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS RowNumber
FROM Employees
WHERE RowNumber = 1;

SQL Server processes WHERE before calculating the SELECT list and its window functions. Therefore, RowNumber does not yet exist when WHERE is evaluated.

Use a CTE:

WITH EmployeeRanking AS
(
    SELECT
        EmployeeName,
        Department,
        Salary,
        ROW_NUMBER() OVER
        (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS RowNumber
    FROM Employees
)
SELECT *
FROM EmployeeRanking
WHERE RowNumber = 1;

30. Real-world uses

Requirement	Suitable function
Number every record	`ROW_NUMBER()`
Rank employees by salary	`RANK()`
Rank without gaps	`DENSE_RANK()`
Divide customers into groups	`NTILE()`
Calculate running balance	`SUM()`
Calculate moving average	`AVG()` with a frame
Compare current and previous sales	`LAG()`
View the next scheduled event	`LEAD()`
Find the first value in a group	`FIRST_VALUE()`
Find the last value in a group	`LAST_VALUE()`
Show department total beside each employee	`SUM()` with `PARTITION BY`
Show top N records per group	Ranking function with CTE

Key points

Window functions use the OVER() clause.
They perform calculations across related rows without combining those rows.
PARTITION BY creates separate calculation groups.
ORDER BY defines the logical processing order.
A frame defines the rows included around the current row.
ROW_NUMBER() always generates unique sequential numbers.
RANK() creates gaps after tied values.
DENSE_RANK() does not create gaps.
LAG() accesses a previous row.
LEAD() accesses a following row.
Use a CTE or subquery to filter window-function results.
Always provide deterministic ordering when individual row order matters.
Explicitly use ROWS for a predictable row-by-row running total.

SQL Window Functions

Window Functions in SQL Server — Sample Data, Queries and Results

1. Create sample table and data

Sample data

2. Aggregate function with GROUP BY

3. Aggregate window function

4. Understanding OVER()

5. SUM() — total salary by department

6. AVG() — average salary by department

7. Multiple aggregate window functions

8. ROW_NUMBER()

9. RANK()

10. DENSE_RANK()

11. Difference between ranking functions

12. Find the highest-paid employee in each department

13. Find all highest-paid employees, including ties

14. Find the top two salaries in each department

15. NTILE()

16. Running total

17. Running total within each department

18. Percentage contribution to department salary

19. Compare salary with department average

20. LAG() — access the previous row

21. Compare with the previous employee’s salary

22. LEAD() — access the next row

23. LAG() within each department

24. FIRST_VALUE()

25. LAST_VALUE()

26. Moving average

27. Window frame options

Current and previous row

Previous, current and next row

From the first row to the current row

Entire partition

28. ROWS versus RANGE

Using ROWS

Using RANGE

29. Why can’t a window function be used directly in WHERE?

30. Real-world uses

Key points

Keep Learning - More From This Category

2. Aggregate function with `GROUP BY`

4. Understanding `OVER()`

5. `SUM()` — total salary by department

6. `AVG()` — average salary by department

8. `ROW_NUMBER()`

9. `RANK()`

10. `DENSE_RANK()`

15. `NTILE()`

20. `LAG()` — access the previous row

22. `LEAD()` — access the next row

23. `LAG()` within each department

24. `FIRST_VALUE()`

25. `LAST_VALUE()`

28. `ROWS` versus `RANGE`

Using `ROWS`

Using `RANGE`

29. Why can’t a window function be used directly in `WHERE`?