How to Use SQL Window Functions to Rank and Compare Records

Introduction

When working with SQL Server, you often need to do more than retrieve rows from a table. You may need to rank employees by salary, find the top three products in each category, compare monthly sales, or calculate a running total.

You can solve some of these problems with subqueries, self-joins, or temporary tables. However, these approaches can make a query harder to read and maintain.

SQL window functions provide a simpler way to perform these calculations. They let you calculate values across a group of related rows without combining those rows into a single result.

For example, you can use a window function to rank employees by salary while keeping every employee in the result. You can also compare the current month's sales with the previous month without joining the sales table to itself.

In this article, we will look at how SQL window functions work, how to use them for ranking and comparisons, and how to avoid common mistakes when using them in SQL Server.

What Are SQL Window Functions?

A window function performs a calculation across a set of rows related to the current row. This set of rows is called a window.

Unlike an aggregate function such as SUM() or AVG(), a window function does not normally reduce multiple rows to one result row. It returns a calculated value for each row in the result.

Consider the following Employees table:

Suppose you want to display every employee along with their rank within their department.

A regular GROUP BY query is not suitable because it would group rows and return summary results. A window function can calculate the rank while keeping all five employees in the output.

The general syntax is:

function_name() OVER (
    PARTITION BY column_name
    ORDER BY column_name
)

The main parts are:

• function_name(): The window function, such as ROW_NUMBER(), RANK(), or SUM().
• OVER: Defines the window used for the calculation.
• PARTITION BY: Divides rows into separate groups. This part is optional.
• ORDER BY: Defines the order in which rows are ranked or processed.

Not every window function requires both PARTITION BY and ORDER BY. The correct combination depends on the calculation you need.

Setting Up a Sample Table

Let's create a table that we can use throughout the examples.

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

INSERT INTO Employees
    (EmployeeId, EmployeeName, Department, Salary)
VALUES
    (1, 'Amit', 'IT', 70000),
    (2, 'Priya', 'IT', 85000),
    (3, 'Rahul', 'IT', 85000),
    (4, 'Neha', 'HR', 60000),
    (5, 'Karan', 'HR', 65000);

The table contains employees from two departments, with some employees earning the same salary.

This makes it useful for understanding how ranking functions handle ties.

1. Use ROW_NUMBER() to Assign a Unique Row Number

ROW_NUMBER() assigns a sequential number to each row based on the order specified in the window.

For example, you can number employees from the highest salary to the lowest salary.

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

The result will look like this:

Priya and Rahul have the same salary, but they receive different row numbers. The EmployeeId column provides a second ordering condition so the result is deterministic when salaries are equal.

This is useful when you need to:

• Assign a unique position to every row.
• Select one record from a group.
• Implement pagination.
• Identify duplicate records before removing them.

Important: ROW_NUMBER() always assigns different numbers to rows within the window. If the ORDER BY columns contain ties, add a unique column when you need consistent results.

2. Use PARTITION BY to Rank Records Within Groups

What if you want to rank employees separately within each department?

This is where PARTITION BY becomes useful.

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

The result will look like this:

Notice that numbering starts again at 1 when the department changes.

Without PARTITION BY, SQL Server ranks all employees together. With PARTITION BY Department, each department gets its own ranking window.

This pattern is useful for reports such as:

• Top employees in each department.
• Best-selling products in each category.
• Latest order for each customer.
• Most recent status for each support ticket.

3. RANK() vs. DENSE_RANK() vs. ROW_NUMBER()

SQL Server provides three commonly used ranking functions. Although they look similar, they handle ties differently.

ROW_NUMBER()

Assigns a unique sequential number to every row.

RANK()

Assigns the same rank to rows with equal ordering values. It leaves gaps after tied ranks.

DENSE_RANK()

Assigns the same rank to tied rows but does not leave gaps.

Let's compare them using salary:

SELECT
    EmployeeName,
    Salary,
    ROW_NUMBER() OVER (
        ORDER BY Salary DESC
    ) AS RowNum,
    RANK() OVER (
        ORDER BY Salary DESC
    ) AS SalaryRank,
    DENSE_RANK() OVER (
        ORDER BY Salary DESC
    ) AS DenseSalaryRank
FROM Employees;

The results are:

Priya and Rahul share rank 1 because their salaries are equal.

With RANK(), Amit receives rank 3 because rank 2 is skipped. With DENSE_RANK(), Amit receives rank 2 because no ranks are skipped.

Choose the function based on the business requirement:

4. Find the Top N Records in Each Group

One of the most useful applications of window functions is finding the top records within each group.

Suppose you need the two highest-paid employees in every department.

You can use ROW_NUMBER() inside a common table expression (CTE) and then filter the result.

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

The CTE calculates a row number for each employee within their department. The outer query then returns only rows numbered 1 and 2.

This approach is useful for dashboards, management reports, and category-based rankings.

However, you should choose the ranking function carefully.

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.