Oracle Row_number analytical function with partition by multiple columns

The below example explains below

(1) Usage of row_number function in oracle

(2) How to partition result set using multiple columns.

***

drop table t1;
create table t1(owner char(10), segment_name char(20), num_rows number, insert_date date);

insert into t1 values (‘SYS’, ‘T1’, 10, sysdate);
insert into t1 values (‘SYS’, ‘T2’, 20, sysdate);
insert into t1 values (‘SYS’, ‘T3’, 30, sysdate);
commit;
insert into t1 values (‘SYS’, ‘T1’, 11, sysdate);
insert into t1 values (‘SYS’, ‘T2’, 21, sysdate);
insert into t1 values (‘SYS’, ‘T3’, 31, sysdate);
commit;
insert into t1 values (‘SYS’, ‘T1’, 12, sysdate);
insert into t1 values (‘SYS’, ‘T3’, 32, sysdate);
commit;

— select owner, segment_name, num_rows, to_char(INSERT_DATE, ‘YYYYMMDD’) from t1;

select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1;

select * from
(
select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1
) ST1
WHERE ST1.rno = 1;

insert into t1 values (‘SYS’, ‘T4’, 40, sysdate);
commit;

select * from
(
select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1
) ST1
WHERE ST1.rno = 1;

create view v_t1 as
select * from
(
select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1
) ST1
WHERE ST1.rno = 1;

select * from v_t1;

insert into t1 values (‘SYS’, ‘T5’, 50, sysdate);
insert into t1 values (‘SYS’, ‘T4’, 41, sysdate);
commit;

select * from v_t1;

***

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: