Window Functions: ROW_NUMBER, RANK and LAG – SQL Server | Lesson 20

Published / updated4 Oct 2026

Introduction

Prize day is coming. The principal wants the top three students in every section, with ties handled fairly. GROUP BY cannot do this: it collapses each section into one row, and you lose the students.

Window functions calculate across a group of rows while keeping every row. In this lesson, you rank students, find the top three per section, build running totals, compare each exam with the previous one, and remove duplicate rows.

By the end of this lesson, you will be able to:

Explain how a window function differs from GROUP BY.
Write the OVER clause with PARTITION BY and ORDER BY.
Choose between ROW_NUMBER, RANK, and DENSE_RANK.
Find the top N rows per group.
Build running totals with SUM ... OVER, and compare rows with LAG.
Remove duplicate rows with ROW_NUMBER.

Why this matters

School reports are full of rankings and comparisons. "Rank in class" on a report card, "top 3 per section" for prizes, "fees collected so far this month" for the accountant, and "improved since the last exam" for the parents' meeting.

Without window functions, each of these needs a complicated self join or several queries glued together in code. With them, each is a few readable lines, and SQL Server does the work in one pass over the data.

The concept

Example: window functions vs GROUP BY

	`GROUP BY`	Window function
Rows in the result	One per group	Every original row
Can show detail and total together	No	Yes
SMS example	Students per section	Each student's rank within the section

Think of a race result board. GROUP BY would print "Section C: 50 runners". A window function prints every runner, with their position next to their name.

Example: the OVER clause

A window function is followed by OVER ( ... ), which defines the window: the rows the calculation looks at.

Example
RANK() OVER (PARTITION BY SectionId ORDER BY Total DESC)

PARTITION BY SectionId splits the rows into groups. Ranking restarts in each section.
ORDER BY Total DESC sets the order inside each group: highest total first.
Leave out PARTITION BY, and the whole result is one window.

Example: the three ranking functions

Function	Ties	Sequence with a tie for 3rd
`ROW_NUMBER()`	Always different numbers	1, 2, 3, 4, 5
`RANK()`	Same rank, then skips	1, 2, 3, 3, 5
`DENSE_RANK()`	Same rank, no gaps	1, 2, 3, 3, 4

Choose by the rule you want. Exam positions usually use RANK: two students tied for third, and the next is fifth. Picking exactly one row per group uses ROW_NUMBER.

Example: a total next to every row

Any aggregate can become a window function by adding OVER. This puts 10-A's Unit Test 1 Mathematics average next to each student's mark, without a subquery:

Example
SELECT
    s.FirstName,
    m.MarksObtained,
    AVG(m.MarksObtained) OVER () AS SectionAverage,
    m.MarksObtained - AVG(m.MarksObtained) OVER () AS Difference
FROM dbo.Marks AS m
INNER JOIN dbo.Enrollments AS e ON e.Id = m.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE m.ExamSubjectId = 8
  AND m.EnrollmentId BETWEEN 13 AND 16
ORDER BY e.RollNumber;

FirstName	MarksObtained	SectionAverage	Difference
Aarav	92.00	84.750000	7.250000
Diya	85.00	84.750000	0.250000
Kabir	67.00	84.750000	-17.750000
Meera	95.00	84.750000	10.250000

Empty brackets, OVER (), mean "all rows of the result". Compare this with Lesson 19, which needed a subquery to get the same average. Here every student keeps their row, and the average sits beside it.

Example: other useful window functions

Function	Returns	SMS use
`LEAD(column)`	The value from the next row	Next exam's marks
`NTILE(4)`	Which quarter of the ordered rows the row falls in	Split a class into four ability groups
`FIRST_VALUE(column)`	The first value in the window	The topper's total, next to every student
`SUM`, `AVG`, `COUNT` with `OVER`	A total for the window, on every row	Each student's share of the section total

Example: where window functions can appear

Window functions run late, at the same stage as SELECT. So they cannot be used in WHERE:

Example
SELECT AdmissionNumber
FROM dbo.Students
WHERE ROW_NUMBER() OVER (ORDER BY Id) <= 3;

Output
Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

The fix is a CTE from Lesson 19: calculate the number in the CTE, then filter on it outside.

Apply it to the SMS

These examples use the 10,000 generated students, because ranking needs many rows. Save the full script below as queries\020-window-functions.sql.

Example: ROW_NUMBER, RANK and DENSE_RANK side by side

Half Yearly totals (six subjects) in section 10-C. The first CTE adds up each student's marks; the second ranks them three ways:

Example
WITH Totals AS (
    SELECT
        s.AdmissionNumber,
        SUM(m.MarksObtained) AS Total
    FROM dbo.Marks AS m
    INNER JOIN dbo.ExamSubjects AS es ON es.Id = m.ExamSubjectId
    INNER JOIN dbo.Enrollments AS e ON e.Id = m.EnrollmentId
    INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
    INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
    WHERE es.ExamId = 2
      AND sec.ClassId = 10
      AND sec.Name = 'C'
    GROUP BY s.AdmissionNumber
),
Ranked AS (
    SELECT
        AdmissionNumber,
        Total,
        ROW_NUMBER() OVER (ORDER BY Total DESC, AdmissionNumber) AS RowNumber,
        RANK()       OVER (ORDER BY Total DESC) AS RankNumber,
        DENSE_RANK() OVER (ORDER BY Total DESC) AS DenseRankNumber
    FROM Totals
)
SELECT AdmissionNumber, Total, RowNumber, RankNumber, DenseRankNumber
FROM Ranked
WHERE RowNumber <= 8
ORDER BY RowNumber;

AdmissionNumber	Total	RowNumber	RankNumber	DenseRankNumber
ADM-2026-09039	432.00	1	1	1
ADM-2026-09023	420.00	2	2	2
ADM-2026-09015	414.00	3	3	3
ADM-2026-09042	414.00	4	3	3
ADM-2026-09034	408.00	5	5	4
ADM-2026-09026	402.00	6	6	5
ADM-2026-09018	396.00	7	7	6
ADM-2026-09045	396.00	8	7	6

Two students tie on 414. The three functions treat the tie differently:

ROW_NUMBER gives them 3 and 4, breaking the tie by admission number.
RANK gives both 3 and skips to 5.
DENSE_RANK gives both 3 and continues with 4.

Example: the top 3 in each section

Add PARTITION BY Section, number the rows inside each section, and keep numbers 1 to 3:

Section	PositionInSection	AdmissionNumber	Total
C	1	ADM-2026-09039	432.00
C	2	ADM-2026-09023	420.00
C	3	ADM-2026-09015	414.00
D	1	ADM-2026-09058	426.00
D	2	ADM-2026-09077	420.00
D	3	ADM-2026-09096	414.00

This "top N per group" pattern works the same for 2 sections or 200. With ROW_NUMBER, each section gets exactly three rows. Use RANK instead if tied students must both get a prize.

Read the full query in the script as three steps:

Totals: one row per student with their Half Yearly total and section.
Ranked: the same rows, each with its position inside its own section.
Final select: keep positions 1 to 3, sorted by section and position.

Each step is a CTE you can run on its own. If a prize list ever looks wrong, run Totals first and check one student's marks by hand; then run Ranked and check the order. This is the step-by-step testing habit from Lesson 19, applied to rankings.

Example: a running total

SUM with OVER (ORDER BY ...) adds up all rows so far:

Example
SELECT
    ReceiptNumber,
    PaidOn,
    Amount,
    SUM(Amount) OVER (ORDER BY PaidOn, ReceiptNumber ROWS UNBOUNDED PRECEDING) AS CollectedSoFar
FROM dbo.FeePayments
WHERE IsDeleted = 0
ORDER BY PaidOn, ReceiptNumber;

ReceiptNumber	PaidOn	Amount	CollectedSoFar
RCP-2026-000001	2026-06-10 05:00:00	45500.00	45500.00
RCP-2026-000003	2026-06-10 05:05:00	39500.00	85000.00
RCP-2026-000002	2026-06-15 06:30:00	20000.00	105000.00

ROWS UNBOUNDED PRECEDING means "from the first row up to this one". Writing it explicitly makes the meaning clear and is faster than the default frame.

Example: compare with the previous exam using LAG

LAG(column) returns the value from the previous row in the window. Two students' Mathematics marks in Unit Test 1 and the Half Yearly:

AdmissionNumber	Exam	MarksObtained	PreviousExam	Change
ADM-2026-09023	Unit Test 1	70.00	NULL	NULL
ADM-2026-09023	Half Yearly	94.00	70.00	24.00
ADM-2026-09039	Unit Test 1	99.00	NULL	NULL
ADM-2026-09039	Half Yearly	42.00	99.00	-57.00

The first exam has no previous row, so LAG returns NULL. LEAD is the mirror image: it reads the next row. A drop of 57 marks is exactly what a class teacher wants flagged before the parents' meeting.

Example: remove duplicate rows

An imported phone list has repeated lines. Number the copies of each admission number and phone with ROW_NUMBER, then keep copy 1:

ImportLine	AdmissionNumber	Phone
1	ADM-2017-0001	9876540101
2	ADM-2017-0002	9876540103
4	ADM-2017-0003	9876540105

Lines 3 and 5 were copies and are gone. The script builds the list with VALUES, so nothing in the database changes. With a real table, the same CTE can feed a DELETE that removes every row whose copy number is above 1.

Show queries/020-window-functions.sql

queries/020-window-functions.sql
-- Lesson 20: window functions. Read-only: it changes nothing.
USE SchoolDb;
GO

-- 1. ROW_NUMBER, RANK and DENSE_RANK side by side:
--    Half Yearly totals in section 10-C, the top rows only.
WITH Totals AS (
    SELECT
        s.AdmissionNumber,
        SUM(m.MarksObtained) AS Total
    FROM dbo.Marks AS m
    INNER JOIN dbo.ExamSubjects AS es ON es.Id = m.ExamSubjectId
    INNER JOIN dbo.Enrollments AS e ON e.Id = m.EnrollmentId
    INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
    INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
    WHERE es.ExamId = 2
      AND sec.ClassId = 10
      AND sec.Name = 'C'
    GROUP BY s.AdmissionNumber
),
Ranked AS (
    SELECT
        AdmissionNumber,
        Total,
        ROW_NUMBER() OVER (ORDER BY Total DESC, AdmissionNumber) AS RowNumber,
        RANK()       OVER (ORDER BY Total DESC) AS RankNumber,
        DENSE_RANK() OVER (ORDER BY Total DESC) AS DenseRankNumber
    FROM Totals
)
SELECT AdmissionNumber, Total, RowNumber, RankNumber, DenseRankNumber
FROM Ranked
WHERE RowNumber <= 8
ORDER BY RowNumber;

-- 2. PARTITION BY: the top 3 students in each of sections 10-C and 10-D.
WITH Totals AS (
    SELECT
        sec.Name AS Section,
        s.AdmissionNumber,
        SUM(m.MarksObtained) AS Total
    FROM dbo.Marks AS m
    INNER JOIN dbo.ExamSubjects AS es ON es.Id = m.ExamSubjectId
    INNER JOIN dbo.Enrollments AS e ON e.Id = m.EnrollmentId
    INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
    INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
    WHERE es.ExamId = 2
      AND sec.ClassId = 10
      AND sec.Name IN ('C', 'D')
    GROUP BY sec.Name, s.AdmissionNumber
),
Ranked AS (
    SELECT
        Section,
        AdmissionNumber,
        Total,
        ROW_NUMBER() OVER (PARTITION BY Section ORDER BY Total DESC, AdmissionNumber) AS PositionInSection
    FROM Totals
)
SELECT Section, PositionInSection, AdmissionNumber, Total
FROM Ranked
WHERE PositionInSection <= 3
ORDER BY Section, PositionInSection;

-- 3. A running total: fees collected so far, payment by payment.
SELECT
    ReceiptNumber,
    PaidOn,
    Amount,
    SUM(Amount) OVER (ORDER BY PaidOn, ReceiptNumber ROWS UNBOUNDED PRECEDING) AS CollectedSoFar
FROM dbo.FeePayments
WHERE IsDeleted = 0
ORDER BY PaidOn, ReceiptNumber;

-- 4. LAG: each student's Mathematics marks in Unit Test 1 and the Half Yearly,
--    with the change since the previous exam (two students from 10-C).
SELECT
    s.AdmissionNumber,
    ex.Name AS Exam,
    m.MarksObtained,
    LAG(m.MarksObtained) OVER (PARTITION BY s.Id ORDER BY ex.StartDate) AS PreviousExam,
    m.MarksObtained - LAG(m.MarksObtained) OVER (PARTITION BY s.Id ORDER BY ex.StartDate) AS Change
FROM dbo.Marks AS m
INNER JOIN dbo.ExamSubjects AS es ON es.Id = m.ExamSubjectId
INNER JOIN dbo.Exams AS ex ON ex.Id = es.ExamId
INNER JOIN dbo.Enrollments AS e ON e.Id = m.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE es.SubjectId = 2
  AND s.AdmissionNumber IN ('ADM-2026-09039', 'ADM-2026-09023')
ORDER BY s.AdmissionNumber, ex.StartDate;

-- 5. Remove duplicates: an imported phone list has repeats; keep the first row of each.
WITH Imported AS (
    SELECT ImportLine, AdmissionNumber, Phone
    FROM (VALUES
        (1, 'ADM-2017-0001', '9876540101'),
        (2, 'ADM-2017-0002', '9876540103'),
        (3, 'ADM-2017-0001', '9876540101'),
        (4, 'ADM-2017-0003', '9876540105'),
        (5, 'ADM-2017-0002', '9876540103')
    ) AS v (ImportLine, AdmissionNumber, Phone)
),
Numbered AS (
    SELECT
        ImportLine,
        AdmissionNumber,
        Phone,
        ROW_NUMBER() OVER (PARTITION BY AdmissionNumber, Phone ORDER BY ImportLine) AS CopyNumber
    FROM Imported
)
SELECT ImportLine, AdmissionNumber, Phone
FROM Numbered
WHERE CopyNumber = 1
ORDER BY ImportLine;
GO

The whole script runs in under a second on the full dataset of 120,000 marks.

Common mistakes

Using a window function in WHERE. Error 4108. Fix: calculate it in a CTE and filter outside.
ROW_NUMBER when ties matter. Tied students get different positions. Fix: use RANK or DENSE_RANK for positions people will see.
ROW_NUMBER without a tie-breaker. Tied rows can swap places between runs. Fix: add a unique column, such as AdmissionNumber, to the ORDER BY.
Forgetting PARTITION BY. Ranking runs across the whole school instead of each section. Fix: partition by the group the ranking belongs to.
Running totals without a frame. The default frame treats equal dates as one step. Fix: write ROWS UNBOUNDED PRECEDING and a unique ORDER BY.

In real projects

From 16 years of .NET projects

Window functions replaced more application code on my projects than any other SQL feature. One team pulled 50,000 rows into C# just to number them per group and keep the top three. The same result came from a ten-line query, and the page went from eight seconds to under one.

The decision I always make explicitly is the tie rule. Before writing RANK or ROW_NUMBER, I ask the school: "If two students tie for third, do both get a prize?"

The answer picks the function. Guessing picks a complaint.

Best practices

Use PARTITION BY for "per section", "per class", or "per student" calculations.
Agree the tie rule first, then choose ROW_NUMBER, RANK, or DENSE_RANK.
Give ROW_NUMBER a unique tie-breaker so results never change between runs.
Filter on window results in an outer query, using a CTE.
Write the frame, ROWS UNBOUNDED PRECEDING, for running totals.

Recap

Window functions calculate across rows but keep every row.
OVER defines the window: PARTITION BY groups, ORDER BY sorts inside.
ROW_NUMBER never ties, RANK ties and skips, DENSE_RANK ties without gaps.
Top N per group: number rows per group in a CTE, then keep numbers up to N.
SUM ... OVER builds running totals; LAG and LEAD read neighbouring rows.
Module 4 is complete: you can now answer any reporting question the school asks.

Homework

Library module task: Rank the books by how many times they have been issued, using DENSE_RANK, so books with equal counts share a rank. Include books that were never issued.

A correct result looks like: "The Jungle Book" and "Wings of Fire" share rank 1 with one issue each, and "Malgudi Days" has rank 2 with none.

Show the solution

library-assignment/020-library-ranking.sql
-- Library homework, Lesson 20. Read-only.
USE SchoolDb;
GO

-- Books ranked by how often they were issued.
SELECT
    b.Title,
    COUNT(i.Id) AS TimesIssued,
    DENSE_RANK() OVER (ORDER BY COUNT(i.Id) DESC) AS Popularity
FROM dbo.Books AS b
LEFT JOIN dbo.BookCopies AS c ON c.BookId = b.Id
LEFT JOIN dbo.BookIssues AS i ON i.BookCopyId = c.Id
GROUP BY b.Title
ORDER BY Popularity, b.Title;
GO

The LEFT JOINs keep "Malgudi Days", and COUNT(i.Id) counts 0 for it, because COUNT(column) skips the NULL from the missing issue.

Quiz

How does a window function differ from GROUP BY?
1. It is faster
2. It keeps every row while adding a calculated value
3. It removes duplicates
4. It only works on numbers
Show answer
Answer: B. It keeps every row while adding a calculated value GROUP BY collapses rows; a window function adds a value to each row.
Two students tie for third. Which function gives the next student rank 5?
1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. LAG
Show answer
Answer: B. RANK RANK gives ties the same rank and then skips.
What does PARTITION BY Section do in RANK() OVER (PARTITION BY Section ORDER BY Total DESC)?
1. Sorts sections alphabetically
2. Restarts the ranking in each section
3. Removes sections with ties
4. Groups rows like GROUP BY
Show answer
Answer: B. Restarts the ranking in each section Each section gets its own ranking starting at 1.
Why can't you write WHERE ROW_NUMBER() OVER (...) <= 3?
1. ROW_NUMBER needs GROUP BY
2. Window functions are only allowed in SELECT and ORDER BY
3. WHERE cannot compare numbers
4. It is allowed
Show answer
Answer: B. Window functions are only allowed in SELECT and ORDER BY Calculate it in a CTE, then filter in the outer query.
What does LAG(MarksObtained) return for a student's first exam?
1. 0
2. The same marks
3. NULL
4. The next exam's marks
Show answer
Answer: C. NULL There is no previous row, so LAG returns NULL.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is the difference between ROW_NUMBER, RANK, and DENSE_RANK? concept

All three number rows in order. ROW_NUMBER always gives different numbers, even for ties. RANK gives ties the same number and then skips (1, 2, 3, 3, 5). DENSE_RANK gives ties the same number without gaps (1, 2, 3, 3, 4).

See the example · Permalink

F How is a window function different from GROUP BY? concept

GROUP BY collapses each group into one row. A window function calculates across a group of rows but keeps every row, so you can show each student with their rank or the section average next to their mark.

See the example · Permalink

F What does PARTITION BY do in an OVER clause? concept

It splits the rows into groups, and the window function restarts for each group. RANK() OVER (PARTITION BY SectionId ORDER BY Total DESC) ranks students within their own section, starting at 1 in every section.

See the example · Permalink

Mid (3-5 yrs)

M How do you remove duplicate rows from a table using ROW_NUMBER? coding

Number the rows within each group of duplicates with ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY a tie-breaker) in a CTE. Rows numbered 1 are kept; delete or ignore the rest.

See the example · Permalink

M How do you calculate a running total in SQL Server? coding

Use SUM(Amount) OVER (ORDER BY PaidOn, ReceiptNumber ROWS UNBOUNDED PRECEDING). Each row gets the total of all rows up to it. A unique ORDER BY and the explicit ROWS frame make the result predictable and fast.

See the example · Permalink

M What do LAG and LEAD do? concept

LAG(column) returns the value from the previous row in the window, and LEAD(column) from the next row. They compare a row with its neighbours, such as this exam's marks against the previous exam's. The first row's LAG is NULL.

See the example · Permalink

Senior (6-9 yrs)

S Write a query for the top 3 students in every section. coding

In a CTE, compute each student's total, then add ROW_NUMBER() OVER (PARTITION BY Section ORDER BY Total DESC, AdmissionNumber). In the outer query, keep rows numbered 1 to 3. Use RANK instead if tied students must both appear.

How to answer: Write the two-step pattern and explain why the filter must be outside (window functions are not allowed in WHERE). Ask about the tie rule before choosing the function, and mention the tie-breaker that keeps results stable.

See the example · Permalink

S A page loads 50,000 rows into C# only to rank them and show the top 3 per group. How would you improve it? scenario

Move the ranking into SQL with a window function and return only the rows the page shows. The database does it in one pass with the data already there, and the network, memory, and page time drop sharply.

How to answer: Measure first (rows transferred, page time), then show the window-function rewrite and the expected gain. Discuss the trade-off: logic in SQL is less visible to C# developers, so keep it in a named view or procedure with tests.

See the example · Permalink

Architect / Lead (10+ yrs)

A Should rankings and running totals be calculated in the database or in application code? design

For large data sets, in the database: it avoids moving rows and uses set-based processing. For small results that the screen already holds, or rules that change often and need unit tests in C#, application code can be simpler. Keep one place responsible for each calculation.

How to answer: Weigh data volume, network cost, testability, and who maintains the logic. Give SMS examples on both sides (prize lists over 10,000 students in SQL; re-sorting 40 rows on a screen in code). Stress consistency: one calculation, one place.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-20-window-functions (code repository coming soon)
Files: queries/020-window-functions.sql, library-assignment/020-library-ranking.sql
Video: coming soon

What's next

Module 4 is done. Module 5 turns your queries into reusable database objects and makes them fast, starting with Lesson 21: Views and functions.

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: window functions vs GROUP BY​

Example: the OVER clause​

Example: the three ranking functions​

Example: a total next to every row​

Example: other useful window functions​

Example: where window functions can appear​

Apply it to the SMS​

Example: ROW_NUMBER, RANK and DENSE_RANK side by side​

Example: the top 3 in each section​

Example: a running total​

Example: compare with the previous exam using LAG​

Example: remove duplicate rows​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: window functions vs GROUP BY

Example: the OVER clause

Example: the three ranking functions

Example: a total next to every row

Example: other useful window functions

Example: where window functions can appear

Apply it to the SMS

Example: ROW_NUMBER, RANK and DENSE_RANK side by side

Example: the top 3 in each section

Example: a running total

Example: compare with the previous exam using LAG

Example: remove duplicate rows

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next