Window Functions Preview: ROW_NUMBER and RANK in SQL

Every aggregate function covered so far in this module collapses multiple rows into one summary row per group — but sometimes you want to rank or number individual rows without losing any of them, like assigning a position to every product within its category by price. This is exactly what window functions do, and this lesson gives a first, practical preview of two of the most common ones: ROW_NUMBER and RANK, ahead of their full deep-dive treatment in Module 13.

What Is a Window Function, at a Glance?

A window function performs a calculation across a set of related rows — called a 'window' — without collapsing those rows into a single summary row, unlike GROUP BY aggregation. ROW_NUMBER() assigns a unique, sequential number to each row within its window. RANK() assigns a rank based on a specified order, giving the same rank to tied rows and leaving gaps in the ranking sequence afterward.

What You'll Learn

• Understand the core difference between window functions and GROUP BY aggregation.
• Use ROW_NUMBER() to assign a unique sequential number to each row within a window.
• Use RANK() to assign a competition-style rank, handling tied values.
• Recognize the PARTITION BY and ORDER BY clauses within the OVER() syntax.

Key Terms to Know

• Window function: A function that calculates a value across a set of related rows without collapsing them into one row.
• OVER(): The clause that defines the window (partitioning and ordering) a window function operates over.
• PARTITION BY: Divides rows into groups (partitions) within the window, similar in spirit to GROUP BY but without collapsing rows.
• ROW_NUMBER() / RANK(): Window functions assigning a sequential number or competition-style rank to each row.

Why Window Functions Are Different from GROUP BY

GROUP BY category with an aggregate function like COUNT(*) collapses every row sharing the same category into a single summary row — you lose the individual rows entirely, left only with the per-group total. A window function instead keeps every individual row in the result, while still calculating something based on a related group of rows around it.

This means a window function can answer questions like 'what is this product's price rank within its own category' for every single product row, simultaneously, in a single query — something GROUP BY alone simply cannot do, since it would collapse the very rows you need ranked.

ROW_NUMBER(): Assigning a Unique Sequential Number

ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) assigns 1 to the most expensive product within each category, 2 to the second most expensive within that same category, and so on, restarting the count at 1 again for each new category. PARTITION BY defines the 'window' grouping (similar in spirit to GROUP BY, but without collapsing rows), and ORDER BY within OVER() defines the order numbers are assigned in.

ROW_NUMBER() always assigns strictly unique, sequential numbers — even if two products are tied on price, one will arbitrarily get a lower number than the other, with no ties in the numbering itself.

RANK(): Competition-Style Ranking with Ties

RANK() OVER (PARTITION BY category ORDER BY price DESC) works similarly to ROW_NUMBER but handles ties differently: if two products within the same category are tied for the highest price, both receive rank 1, and the next distinct price receives rank 3 (skipping rank 2 entirely) — exactly like ranking in a competition where tied competitors share the same placement, and the next placement accounts for the tie.

A closely related function, DENSE_RANK(), behaves like RANK() for tied values but doesn't leave gaps afterward — the next distinct price after a tie at rank 1 would receive rank 2, not rank 3. The full depth of window functions, including these and several others, is covered comprehensively in Module 13 later in this course.

Visual Summary

Picture a row of runners crossing a finish line, all from the same race (the 'window'). ROW_NUMBER hands out bib-style sequential numbers 1, 2, 3, 4... to every runner in order, even if two runners technically crossed at the exact same instant. RANK instead acts like an official competition judge: two runners crossing simultaneously both get awarded 1st place together, and the very next runner is awarded 3rd place, since 2nd place was already 'used up' by the tie.

ROW_NUMBER vs RANK (Tied Values)

Price Rank Position	ROW_NUMBER()	RANK()
1st (tied price)	1	1
2nd (tied price)	2	1 (tied with above)
3rd (next distinct price)	3	3 (rank 2 is skipped)
4th	4	4

SQL Example

-- ROW_NUMBER: unique sequential rank of each product's price within its category
SELECT product_name, category, price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS price_position
FROM products;

-- RANK: competition-style ranking that handles tied prices
SELECT product_name, category, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;

-- Using ROW_NUMBER to find just the single most expensive product per category
SELECT * FROM (
  SELECT product_name, category, price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) ranked
WHERE rn = 1;

The first two queries show ROW_NUMBER and RANK applied to the same ranking scenario, differing only in how they handle tied prices within each category. The third demonstrates a genuinely powerful, common pattern: using ROW_NUMBER inside a subquery, then filtering for rn = 1 in the outer query to retrieve just the single top-priced product per category — something that would be considerably more awkward to express using only GROUP BY and MAX.

Real-World Examples

• E-commerce platforms use ROW_NUMBER to find the single most recent order per customer, or the top-priced product per category.
• Sales leaderboards use RANK to display competition-style standings where tied salespeople genuinely share the same rank position.
• Analytics teams use window functions to calculate a row's position within a related group without collapsing the underlying detail rows, unlike GROUP BY.
• Subscription platforms use ROW_NUMBER to identify and retain only the most recent record per customer when cleaning up duplicate historical entries.

Best Practices and Pro Tips

• Recognize the signal for needing a window function rather than GROUP BY: if you need a per-row calculated value (like a rank or running position) while still keeping every individual row in the output, GROUP BY alone can't do that — a window function can.
• Use ROW_NUMBER when you need strictly unique sequential numbering even among ties; use RANK (or DENSE_RANK) specifically when ties should be reflected meaningfully in the ranking itself.
• Treat this lesson as a conceptual preview — the full PARTITION BY, ORDER BY, frame clause syntax, and many additional window functions are covered comprehensively in Module 13, so don't worry about memorizing every detail yet.

Common Mistakes to Avoid

• Trying to solve a 'top N per group' problem using only GROUP BY and aggregate functions, when a window function with ROW_NUMBER is the much cleaner, more direct tool.
• Confusing ROW_NUMBER's strictly unique numbering with RANK's tie-aware numbering, leading to incorrect assumptions about how ties will be handled.
• Forgetting that PARTITION BY (within OVER()) is what restarts the numbering for each group — omitting it causes the function to number across the entire result set as one single window instead.
• Assuming window functions collapse rows the same way GROUP BY does — they don't; every original row remains in the output, just with an added calculated column.

Interview Questions

Q1. What is the fundamental difference between a window function and GROUP BY aggregation?

GROUP BY collapses rows sharing the same group value into a single summary row per group. A window function calculates a value across a related set of rows (a 'window') without collapsing them, so every original row remains in the result.

Q2. What is the difference between ROW_NUMBER() and RANK() when there are tied values?

ROW_NUMBER() always assigns strictly unique, sequential numbers even among tied values, arbitrarily breaking the tie. RANK() assigns the same rank to tied values and then skips ahead in the ranking sequence afterward, similar to competition-style ranking.

Q3. What does PARTITION BY do within a window function's OVER() clause?

PARTITION BY divides the rows into separate groups (partitions) for the purpose of the window calculation, causing functions like ROW_NUMBER to restart their numbering independently within each partition, conceptually similar to GROUP BY but without collapsing rows.

Q4. How could ROW_NUMBER be used to find the single highest-priced product in each category?

By computing ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) in a subquery, then filtering the outer query for rows where that row number equals 1, isolating just the top-priced product within each category.

Practice MCQs

1. What is the key difference between a window function and GROUP BY?

1. Window functions always run faster than GROUP BY
2. Window functions collapse rows; GROUP BY does not
3. Window functions calculate across related rows without collapsing them, unlike GROUP BY
4. There is no real difference

Answer: C. Window functions calculate across related rows without collapsing them, unlike GROUP BY

Explanation: Window functions preserve every original row in the output while still calculating a value across a related set of rows, unlike GROUP BY which collapses rows into one summary per group.

2. If two products are tied for the highest price in their category, what does RANK() assign them?

1. Different sequential ranks
2. The same rank, with the next rank skipped afterward
3. An error
4. A rank of 0

Answer: B. The same rank, with the next rank skipped afterward

Explanation: RANK() gives tied values the same rank and then skips ahead in the sequence for the next distinct value, similar to competition-style ranking.

3. What does PARTITION BY do within an OVER() clause?

1. Sorts the entire result set
2. Divides rows into separate groups for the window calculation
3. Removes duplicate rows
4. Filters rows before aggregation

Answer: B. Divides rows into separate groups for the window calculation

Explanation: PARTITION BY splits rows into partitions so window functions like ROW_NUMBER or RANK restart their calculation independently within each partition.

Quick Revision Points

• Window functions calculate across related rows without collapsing them, unlike GROUP BY.
• ROW_NUMBER() assigns strictly unique sequential numbers, even breaking ties arbitrarily.
• RANK() assigns equal ranks to ties and skips subsequent rank numbers afterward.
• PARTITION BY restarts the window calculation for each group; full depth covered in Module 13.

Conclusion

• Window functions solve an entirely different class of problem than GROUP BY aggregation.
• ROW_NUMBER and RANK differ specifically in how they treat tied values.
• This lesson is intentionally a preview — full window function mastery comes later in Module 13.

Window functions like ROW_NUMBER and RANK calculate a value across a related group of rows without collapsing them the way GROUP BY does, making per-row ranking and positioning possible while keeping every original row intact in the result. ROW_NUMBER assigns strictly unique sequential numbers, while RANK gives tied values equal rank with subsequent gaps, much like competition standings. This module's final lesson now brings everything together — string, numeric, date, aggregate, NULL-handling, conditional, type conversion, and JSON functions — into one comprehensive practical exercise against a realistic sales database.