Forum

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

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


Posts: 89
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 4 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:

EmployeeID Name Department Salary
1 Alice HR 50000
2 Bob IT 70000
3 Charlie HR 52000
4 Diana IT 68000
5 Eva HR 49000

Query: Assign Row Numbers to All Employees

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

Output:

Name Department Salary RowNumber
Bob IT 70000 1
Charlie HR 52000 2
Diana IT 68000 3
Alice HR 50000 4
Eva HR 49000 5

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:

Name Department Salary DepartmentRank
Bob IT 70000 1
Diana IT 68000 2
Charlie HR 52000 1
Alice HR 50000 2
Eva HR 49000 3

3. Identifying and Removing Duplicates

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

Example: Sales Data with Duplicates

SaleID ProductName Price
1 Laptop 1000
2 Phone 500
3 Laptop 1000
4 Tablet 700
5 Laptop 1000

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:

SaleID ProductName Price RowNumber
3 Laptop 1000 2
5 Laptop 1000 3

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

OrderID CustomerID OrderDate Amount
1 101 2025-01-01 100
2 102 2025-01-03 200
3 101 2025-01-05 150
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:

OrderID CustomerID OrderDate Amount RowNumber
3 101 2025-01-05 150 1
2 102 2025-01-03 200 1

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.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: