Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Rank and Row

Rank and Row

Learn to assign ranks or row numbers to ordered data using SQL’s Rank and Row pattern for top-N queries, tie handling, and group-wise sorting.

We'll cover the following...

When analyzing sales data, we might want to find the top 3 best-selling products in each category or determine who placed the earliest order among all customers. These kinds of questions require more than just filtering or grouping; we need to assign positions or row numbers to our data based on a sorting rule.

This is where the Rank and row pattern becomes essential.

In this lesson, we’ll learn how to rank rows, assign row numbers, and manage ties. We'll explore common use cases, understand the structure of ranking functions, and work with examples from our course database. By the end, we’ll be comfortable with SQL's ranking tools and know when and how to use them effectively.

In this lesson, we will:

  • Understand the purpose and structure of ranking functions in SQL.

  • Learn how to assign ranks and row numbers to ordered data.

  • Practice using RANK(), DENSE_RANK(), and ROW_NUMBER().

  • Solve common ranking problems using real-world data.

Pattern overview

Category:

  • Sequencing & Hierarchical Patterns

Intent:

To assign a position to each row based on a specific order, optionally grouped by categories.

Motivation:

There are many real-world scenarios where we need to identify the first, top, or ranked items in a dataset. For example:

  • What are the top 3 best-selling products per category?

  • Which orders were placed first by each customer?

  • Who are the top 5 VIP customers by loyalty points?

SQL’s ranking functions allow us to answer these questions cleanly without subqueries or procedural loops.

Also known as:

  • Ranking Functions

  • Row Numbering

  • Top-N per Group

Structure

SELECT ...,
       RANK() OVER (PARTITION BY group_column ORDER BY sort_column DESC) AS rank_value,
       ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) AS row_num,
       DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column DESC) AS dense_rank_value
FROM   table_name
Structure for Rank and row pattern

  • RANK() skips ranks when ties occur (e.g., 1, 1, 3).

  • DENSE_RANK() assigns the same rank to ties but doesn’t skip (e.g., 1, 1, 2).

  • ROW_NUMBER() gives each row a unique number regardless of ties (e.g., 1, 2, 3).

Keywords

RANK(), DENSE_RANK(), ROW_NUMBER(), OVER, PARTITION BY, ORDER BY

Problem Structure

We use the Rank and Row pattern when:

  • We need to assign a rank or row number to each record within a group.

  • We define how records should be grouped using PARTITION BY.

  • We specify the order within each group using ORDER BY.

  • We use ranking functions like ROW_NUMBER(), RANK(), or DENSE_RANK() to assign positions.

  • We filter on rank when we want only the top N records per group.

  • Look for keywords like: “top N,” “rank within group,” “first/last per group,” “row number,” or “position in sorted group” to identify when this pattern applies.

Example use cases

1. Top-selling products by category

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products.

Also, the following structure of Categories table:

Field

Type

CategoryID

int

CategoryName

varchar(50)

Where CategoryID is the primary ...