Overview of ROW_NUMBER Function in SQL with Easy Examples

Summary: The ROW_NUMBER function in SQL assigns a unique number to each row, helping sort, rank, and remove duplicates. This blog explains its syntax, practical use cases, and how to combine it with PARTITION BY and ORDER BY for powerful data handling. It is ideal for data science beginners.

Introduction

Ever wondered how SQL keeps track of rows like a pro? Welcome to the Overview of ROW_NUMBER Function in SQL—your fun little guide to understanding one of SQL’s coolest tricks! 

In this blog, I’ll walk you through what the ROW_NUMBER function does and show you why it’s super useful when working with data. Whether you’re trying to rank, sort, or pick out specific rows, this function has your back. 

By the end, you’ll know exactly how and when to use it—even if you’ve never written a single line of SQL before!

Key Takeaways

• ROW_NUMBER assigns a unique number to each row, starting at 1.
• Use PARTITION BY to reset row numbering within groups.
• Combine with ORDER BY to define row ranking order.
• Ideal for removing duplicates, ranking, and pagination tasks.
• Frequently used in data science and analytics workflows.

What is the ROW_NUMBER Function?

The ROW_NUMBER function in SQL assigns a unique number to each row in a result set. This number starts at 1 and increases as you move down the rows. This function helps when you want to sort your data and keep track of the row positions.

Syntax of ROW_NUMBER

Here’s how you write it:

• ROW_NUMBER() is the function.
• OVER tells SQL to apply the function across the rows.
• ORDER BY decides how the numbering is done, based on which column and in what order.

How is it Different from RANK and DENSE_RANK?

Although all three functions add numbers to rows, they behave differently when there are duplicate values:

• ROW_NUMBER gives a unique number to each row, even if the values are the same.
  RANK gives the same rank to tied rows, but it jumps numbers afterward.
• DENSE_RANK also gives the same rank to tied rows but does not skip any numbers.

So, if you want a clean list where every row has a different number, ROW_NUMBER is the best choice.

How ROW_NUMBER Works with PARTITION BY

The ROW_NUMBER() function becomes more powerful when used with the PARTITION BY clause. It allows us to restart the row numbering for each group of rows. This is helpful when you want to organise data into smaller groups and assign row numbers starting from 1 within each group.

Let’s break this down so anyone can understand.

What is PARTITION BY?

Think of PARTITION BY as a way to group your data before numbering it. Imagine you have a table of students from different classes and want to assign a rank to each student based on their marks — within their own class.

Using PARTITION BY class, you tell SQL to start counting row numbers from 1 for each class separately. Without PARTITION BY, SQL will number all the rows as one big group, regardless of the class.

Example: Ranking students within each class

Why is it Useful?

Here are some real-world situations where PARTITION BY becomes useful:

• Ranking Within Groups: Suppose you’re working with a sales report and want to find the top salesperson in each region. By using PARTITION BY region, SQL will rank salespeople separately for each region.
• Filtering Duplicate Records: If your data contains duplicates, use ROW_NUMBER() with PARTITION BY on repeated values (like email or user ID) to identify and remove extra copies while keeping only the first one.
• Pagination by Category: When you want to show a limited number of items per category on a webpage, PARTITION BY helps you list the first few rows from each category.

Example: Removing duplicate emails

Using ORDER BY with ROW_NUMBER

The ORDER BY clause plays a vital role when you use the ROW_NUMBER() function in SQL. It tells SQL how to arrange your data before assigning row numbers. Without ORDER BY, SQL won’t know which row should be numbered first, second, third, etc. So, if you want your row numbers to make sense, you must use ORDER BY correctly.

Why ORDER BY Matters

Think of a list of names. If you want to give each name a number based on who comes first alphabetically, you’ll need to sort the list first. Similarly, the ORDER BY clause sorts the data in SQL before assigning row numbers.

Let’s say you have a table of students with their marks. If you want to assign row numbers starting from the student with the highest marks, you would use ORDER BY marks DESC. This way, the top scorer gets row number 1.

If you skip ORDER BY, SQL might assign row numbers randomly, and your results won’t be helpful.

Example with Ascending Order

Here’s a simple example:

In this case, students with the lowest marks get row number 1, which increases as marks increase.

Example with Descending Order

Now, let’s sort from highest to lowest:

Now, the student with the highest marks gets row number 1.

Practical Examples in Different Scenarios

The ROW_NUMBER function in SQL is not just a fancy feature—it helps solve real problems smartly and simply. Here are three practical situations where this function comes in handy. These examples will help you understand how and why you might use it in everyday data tasks, even if you’re not a tech expert.

Removing Duplicates from a Dataset

Imagine you have a table that accidentally has the same entry more than once. For example, a customer named “John Smith” might appear with the same email ID three times. You can use ROW_NUMBER to give a unique number to each row that looks the same. Then, you keep the first one and delete the rest.

Here’s how it works:
SQL will assign a number like 1, 2, 3 to the duplicate rows. You keep the row with number 1 and remove the others. It’s like sorting your papers and keeping just one copy of each.

Fetching Top-N Records per Group

Let’s say you run a bookstore and want to see the best-selling book in each category, like Fiction, Science, and History. Using ROW_NUMBER, you can group the books by category and assign a number based on how many copies were sold. Then, you just pick the one with ROW_NUMBER = 1 from each group.

It’s a quick way to find “the top one” in every group without sorting the whole table.

Pagination Using ROW_NUMBER

Websites often show data in pages—like 10 results at a time. If your table has 1,000 rows, you don’t want to show everything at once. ROW_NUMBER helps break data into smaller pages by assigning numbers to each row. Then, you display only the rows that match the current page (like 11 to 20 for page 2).

Bringing It Full Circle

Mastering the ROW_NUMBER function gives you greater control over your SQL queries—whether you’re cleaning duplicates, ranking data, or implementing pagination. This function is handy in real-world data science tasks where clean and organised datasets are crucial for analysis. 

If you’re stepping into the world of data or want to sharpen your SQL skills, start exploring data science courses by Pickl.AI. Learn from industry experts, work on real datasets, and get hands-on with tools like SQL, Python, and more. 

A strong grip on SQL functions like ROW_NUMBER can set the foundation for successful data-driven decision-making.

Frequently Asked Questions

What does the ROW_NUMBER function do in SQL?

The ROW_NUMBER function assigns a unique sequential number to each row in a result set. It’s useful for sorting, ranking, and pagination, especially when combined with PARTITION BY and ORDER BY. It helps data analysts structure data more efficiently for analysis and reporting.

How is ROW_NUMBER different from RANK in SQL?

While both functions assign numbers to rows, ROW_NUMBER gives each row a unique number—even for duplicates. In contrast, RANK assigns the same rank to tied rows but skips the next rank(s). Choose ROW_NUMBER when you want a distinct sequence without gaps.

Can ROW_NUMBER be used to remove duplicates in SQL?

Yes, ROW_NUMBER is commonly used to remove duplicate records. You can assign row numbers to grouped data using PARTITION BY, then filter out rows where the row number is greater than 1. This helps retain only the first occurrence of each duplicate entry.