Row_number function in Oracle with examples
Contents
Row_number function in Oracle
Row_number is an analytical function. It assigns a unique sequential number to each row according to its ordering within a group of rows. The row number starts at 1 and continues up sequentially.
Row_number Syntax
|
1 2 |
ROW_NUMBER() OVER (PARTITIONED BY <column_reference> ORDER BY <sort_expression> ASC/DESC) |
- column_reference is a column name that is used to divide the result set into partitions.
- sort_expression is an another column that determines the order in which ROW_NUMBER values are applied to the rows.
Example
Consider that we have a table called shoe_details in Oracle. It has the following columns Shoe_Id, Brand_Name, Shoe_Category and Price_in_usd.
row_number() over partition by in Oracle
As mentioned below, the table has the price details of different shoes. For this example, we have taken two kind of shoes such as Sneakers and Ballet flats. Our goal is to find the lowest price of shoe and its details from each category.
Since we need to find the shoes based on the category, the column reference for the partition is Shoe_Category. Within each partition, the row number can be assigned to each row.
The order of the row in each partition is defined using the ORDER BY clause. For that, we will use the column Price_in_usd. By default, ORDER BY clause sort the rows in ascending order. Let’s write a query with row number function.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Shoe_Id, Brand_Name, Shoe_Category, Price_in_usd, ROW_NUMBER() OVER (PARTITION BY Shoe_Category ORDER BY Price_in_usd ) as row_num FROM shoe_details; |
As we shown above, the query returned the result set with the row number. Within each shoe Category “Ballet flats” & “Sneakers“, the rows are sorted based on Price_In_USD values. For the sorted rows, the row numbers are assigned.
In each category, the lowest price of shoe got the row number as 1. Then it is incremented sequentially. Now we can fetch only the lowest price of shoe from each category.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT Shoe_Id, Brand_Name, Shoe_Category, Price_in_usd FROM ( SELECT Shoe_Id, Brand_Name, Shoe_Category, Price_in_usd, ROW_NUMBER() OVER (PARTITION BY Shoe_Category ORDER BY Price_in_usd ) as row_num FROM shoe_details ) final_result where final_result.row_num = 1; |
Using sub query , we have fetched only the rows which has the row number as 1. It produced the lowest price of shoe from each category.
row_number() over partition by and order by descending in Oracle
Similar to lowest price, we can fetch the highest price of shoe from each category using Row_Number function. For that, we need to change the order of the price in descending order.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Shoe_Id, Brand_Name, Shoe_Category, Price_in_usd, ROW_NUMBER() OVER (PARTITION BY Shoe_Category ORDER BY Price_in_usd DESC ) as row_num FROM shoe_details; |
As we shown above, the row number 1 is assigned to highest price of shoe in each category. Now we can filter those rows using where condition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT Shoe_Id, Brand_Name, Shoe_Category, Price_in_usd FROM ( SELECT Shoe_Id, Brand_Name, Shoe_Category, Price_in_usd, ROW_NUMBER() OVER (PARTITION BY Shoe_Category ORDER BY Price_in_usd DESC ) as row_num FROM shoe_details ) final_result where final_result.row_num = 1; |
At the end, we are able to get the highest and lowest price of shoe in each category using Row_number function. Similar to this, we can filter the range of row numbers to get the top 3 highest/lowest price details.
