Sunday, October 5, 2014

Use of Row_Number function in PL/SQL

1 comment
Row_Number is an analytical function which assigns a unique number to the rows defined in the Partition by clause. These rows are ordered in the sequence of the column mentioned in the order by clause.

Syntax of Row_Number():
Row_number() over(partition by col1, col2, .. order by col3 desc)

Example:
Suppose you have a product table as:
create table product(
product_name varchar2(20),
product_type varchar2(20),
mfg_date date
);

Insert values in the table:

insert into product values('Soap', 'Homecare', '01-Jul-2014');
insert into product values('Soap', 'Homecare', '02-Jul-2014');
insert into product values('Soap', 'Homecare', '03-Jul-2014');
insert into product values('Maggi', 'Snacks', '04-Jul-2014');
insert into product values('Maggi', 'Snacks', '05-Jul-2014');
insert into product values('Shampoo', 'Homecare', '06-Jul-2014');




Suppose we want to pick up same product with same product type with latest manufacturing date.
 We can query the table using row_number and get assign number to each row as follows:

SELECT product_name,
  product_type,
  mfg_date,
  row_number() over (partition by product_name, product_type order by mfg_date DESC)
FROM product;



Now we can see that all the rows with row_number values as 1 are the ones with latest manufacturing dates. Using this value we can pick up the latest manufactured products.

1 comment: