Forum

Forums
SQL
Interview Questions...
"How to Use SQL ROW...
 
Share:
Notifications
Clear all

"How to Use SQL ROW_NUMBER() Function for Advanced Data Ranking"

 
Interview Questions & Answers
Last Post by SQL Admin 1 year ago
1 Posts
1 Users
0 Reactions
885 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jan 13, 2025 1:13 pm
(@sql-admin)
Reputable Member
Joined: 6 years ago

Introduction
The SQL ROW_NUMBER() function is a powerful window function used for assigning unique sequential numbers to rows within a result set, based on a specific order. This function is widely used for ranking, pagination, and identifying duplicates in datasets. In this post, we’ll dive into the syntax, usage, and practical examples of the ROW_NUMBER() function.

What is the ROW_NUMBER() Function?

The ROW_NUMBER() function assigns a unique integer to each row in a result set, starting from 1 for the first row in each partition.

Syntax:

sql
 
ROW_NUMBER() OVER (
[PARTITION BY column_name]
ORDER BY column_name
)
  • PARTITION BY: Divides the result set into partitions, and the ROW_NUMBER() function restarts the numbering for each partition.
  • ORDER BY: Specifies the order of rows in each partition.

1. Basic Example of ROW_NUMBER()

Consider the Employees table:

EmployeeIDNameDepartmentSalary
1AliceHR50000
2BobIT70000
3CharlieHR52000
4DianaIT68000
5EvaHR49000

Query: Assign Row Numbers to All Employees

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

Output:

NameDepartmentSalaryRowNumber
BobIT700001
CharlieHR520002
DianaIT680003
AliceHR500004
EvaHR490005

2. Using ROW_NUMBER() with PARTITION BY

Query: Rank Employees Within Each Department

sql
 
SELECT Name, Department, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DepartmentRank
FROM Employees;

Output:

NameDepartmentSalaryDepartmentRank
BobIT700001
DianaIT680002
CharlieHR520001
AliceHR500002
EvaHR490003

3. Identifying and Removing Duplicates

The ROW_NUMBER() function can help identify duplicate rows in a table.

Example: Sales Data with Duplicates

SaleIDProductNamePrice
1Laptop1000
2Phone500
3Laptop1000
4Tablet700
5Laptop1000

Query: Find and Mark Duplicate Rows

sql
 
WITH RankedSales AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ProductName, Price ORDER BY SaleID) AS RowNumber
FROM Sales
)
SELECT *
FROM RankedSales
WHERE RowNumber > 1;

Output:

SaleIDProductNamePriceRowNumber
3Laptop10002
5Laptop10003

Query: Remove Duplicates While Keeping the First Entry

sql
 
WITH RankedSales AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ProductName, Price ORDER BY SaleID) AS RowNumber
FROM Sales
)
DELETE FROM Sales
WHERE SaleID IN (
SELECT SaleID
FROM RankedSales
WHERE RowNumber > 1
);

4. Pagination Using ROW_NUMBER()

Query: Fetch Page 2 Data (Assuming 3 Records per Page)

sql
 
WITH PaginatedData AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNumber
FROM Employees
)
SELECT *
FROM PaginatedData
WHERE RowNumber BETWEEN 4 AND 6;

5. Combining ROW_NUMBER() with Joins

You can use ROW_NUMBER() in more complex queries involving joins to rank or filter data.

Example: Retrieve the Latest Order for Each Customer

OrderIDCustomerIDOrderDateAmount
11012025-01-01100
21022025-01-03200
31012025-01-05150
sql
 
WITH RankedOrders AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNumber
FROM Orders
)
SELECT *
FROM RankedOrders
WHERE RowNumber = 1;

Output:

OrderIDCustomerIDOrderDateAmountRowNumber
31012025-01-051501
21022025-01-032001

Conclusion

The ROW_NUMBER() function is an essential tool for SQL developers, enabling advanced data analysis and management tasks like ranking, deduplication, and pagination. Start applying ROW_NUMBER() in your queries to handle complex scenarios efficiently. For more SQL tips and tricks, visit our SQL forum.


Topic Tags
SQL ROW_NUMBER() example advanced SQL functions SQL ranking functions ROW_NUMBER() with PARTIT SQL pagination

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  SQL ROW_NUMBER() example (1) , advanced SQL functions (1) , SQL ranking functions (3) , ROW_NUMBER() with PARTIT (1) , SQL pagination (1) ,
Share: