How to Use SQL Window Functions to Rank and Compare Records

If you need exactly two employees per department, ROW_NUMBER() is appropriate. If you need every employee tied for one of the top two distinct salary positions, DENSE_RANK() may be a better choice.

For example, if three employees share the highest salary, DENSE_RANK() <= 2 returns all three along with employees earning the second-highest distinct salary.

5. Use LAG() to Compare a Row with the Previous Row

Ranking is not the only task window functions can handle. You can also compare values across rows.

LAG() returns a value from a preceding row in the same window. It is particularly useful for comparing sales, prices, balances, and monthly totals.

Imagine you have a monthly sales table:

CREATE TABLE MonthlySales
(
    SalesMonth DATE PRIMARY KEY,
    TotalSales DECIMAL(12, 2)
);

INSERT INTO MonthlySales
    (SalesMonth, TotalSales)
VALUES
    ('2026-01-01', 100000),
    ('2026-02-01', 120000),
    ('2026-03-01', 95000),
    ('2026-04-01', 140000);

To compare each month's sales with the previous month's sales, use LAG().

SELECT
    SalesMonth,
    TotalSales,
    LAG(TotalSales) OVER (
        ORDER BY SalesMonth
    ) AS PreviousMonthSales,
    TotalSales - LAG(TotalSales) OVER (
        ORDER BY SalesMonth
    ) AS SalesDifference
FROM MonthlySales
ORDER BY SalesMonth;

The output will be similar to this:

The first row has no previous month in the result, so LAG() returns NULL.

You can also provide a default value:

LAG(TotalSales, 1, 0) OVER (
    ORDER BY SalesMonth
)

Here, 1 means one row back, and 0 is returned when there is no preceding row.

Keep in mind that LAG() looks at the previous row in the ordered result, not necessarily the previous calendar month. If March is missing from the table, April may be compared with February. If your report requires every calendar month, first build a complete monthly series and handle missing months appropriately.

6. Use LEAD() to Look at the Next Row

LEAD() works in the opposite direction to LAG(). It returns a value from a following row.

For example, you may want to compare this month's sales with the next month's sales.

SELECT
    SalesMonth,
    TotalSales,
    LEAD(TotalSales) OVER (
        ORDER BY SalesMonth
    ) AS NextMonthSales
FROM MonthlySales
ORDER BY SalesMonth;

The last row returns NULL because there is no following row in the result.

LEAD() is useful when you need to:

• Compare current and upcoming periods.
• Find the next scheduled event.
• Calculate the time until the next transaction.
• Identify changes between consecutive records.

Both LAG() and LEAD() support an offset and an optional default value.

7. Calculate Running Totals with SUM() OVER()

A running total shows the accumulated value as you move through an ordered set of rows.

For example, you may want to see how total sales grow month by month.

SELECT
    SalesMonth,
    TotalSales,
    SUM(TotalSales) OVER (
        ORDER BY SalesMonth
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS RunningTotal
FROM MonthlySales
ORDER BY SalesMonth;

The result will be:

The window frame defines which rows contribute to the calculation.

• UNBOUNDED PRECEDING starts at the first row in the window.
• CURRENT ROW ends at the current row.

Together, these clauses calculate the total from the first month through the current month.

The explicit ROWS frame also helps avoid unexpected behavior when the ordering column contains duplicate values. For running totals, use a suitable ordering column and include a unique tie-breaker when needed.

8. Compare Each Row with the Group Average

You can use aggregate functions as window functions, too.

Suppose you want to show each employee's salary alongside the average salary in their department.

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, Salary DESC;

The average is calculated separately for each department, but every employee remains in the result.

This differs from a regular aggregate query:

SELECT
    Department,
    AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

The GROUP BY query returns one row per department. The window-function query returns one row per employee, with the department average repeated for each employee.

This approach is useful when a report needs both individual records and group-level statistics.

9. Filter Window Function Results Correctly

A common mistake is trying to use a window function directly in the WHERE clause.

For example, this query is not valid in SQL Server:

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

The problem is that WHERE is processed before the SELECT list calculates the row number. The alias RowNum is therefore not available to the WHERE clause.

Use a CTE or derived table instead:

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

This returns the highest-paid employee in each department, with a deterministic tie-breaker.

If you need all employees tied for the highest salary, use RANK() instead of ROW_NUMBER() and filter for rank 1.

Common Mistakes When Using Window Functions

Performance Tips for SQL Window Functions

Window functions can make SQL queries easier to understand, but they still need to process data. Large tables and complex ordering requirements can affect performance.

Consider these practices when using them in production.

1. Index the Columns Used for Partitioning and Ordering

If a query frequently ranks employees by department and salary, an index that starts with Department and includes Salary may help SQL Server access the relevant rows more efficiently.

For example:

CREATE INDEX IX_Employees_Department_Salary
ON Employees (Department, Salary DESC)
INCLUDE (EmployeeId, EmployeeName);

Whether this index improves performance depends on the query, the table size, the existing indexes, and the execution plan. Do not add indexes without checking their storage and write costs.

2. Avoid Returning Unnecessary Columns

Window functions may require SQL Server to sort or process many rows. Selecting only the columns needed for the report can reduce the amount of data that must be read and returned.

3. Check the Execution Plan

Use the actual execution plan to look for expensive sorts, scans, memory grants, and spills to tempdb.

If a query is slow, examine the execution plan and the available indexes before rewriting the window function. The function itself is not always the main cause of the problem.

4. Use Consistent Ordering

When a calculation depends on row order, make that order explicit. Add a unique tie-breaker if the primary ordering column is not unique.

This is especially important for ROW_NUMBER(), LAG(), LEAD(), and running totals.

5. Test with Production-Like Data

A window query that performs well on a few hundred rows may behave differently on millions of rows. Test with representative data volumes and inspect logical reads, elapsed time, and the actual execution plan.

When Should You Use Window Functions?

Window functions are a good choice when you need to calculate values across related rows while preserving the individual records.

Window functions are not a replacement for every GROUP BY, join, or subquery. Choose the approach that best matches the required result and test its performance.

Conclusion

SQL window functions make it easier to rank records, compare values, and calculate totals without losing individual rows from the result.

Use ROW_NUMBER() when every row needs a unique number, RANK() or DENSE_RANK() when ties matter, and LAG() or LEAD() when you need to compare neighboring rows. Aggregate window functions such as SUM() OVER() and AVG() OVER() are useful when you need running totals or group-level calculations alongside individual records.

For production queries, pay attention to partitioning, ordering, tie handling, and performance. A well-written window query can replace complicated joins and make reports much easier to maintain.