Oracle 11g
Hi Gurus
I appreciate if someone help me out. I have the following sample data:
Sample data
CREATE SEQUENCE file_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER NOCYCLE ;
/
create table test4
(
file_number number,
seq_no number
);
/
insert into test4
select 10,null from dual union all
select 10,null from dual union all
select 10,null from dual union all
select 20,null from dual union all
select 20,null from dual union all
select 30,null from dual
;
/
select file_number,dense_rank() over (order by file_number) dnk
from test4
/
Query Output
FILE_NUMBER, DNK
10 1
10 1
10 1
20 2
20 2
30 3
Required Output
FILE_NUMBER, DNK
10 100
10 100
10 100
20 101
20 101
30 103
Rules:
If you noticed that I created sequence name as file_SEQ, system should user this sequence to generate dnk column and if file_number change then get next sequence.
So far I created the below query but it is not working.
Query
select file_number,dense_rank() over (order by file_number) + file_SEQ.nextval dnk
from test4
Query Output
10 101
10 102
10 103
20 105
20 106
30 108
but due to nextval , it is creating counting without grouping of file_number.