Dear Seniors,
I need your help to write a query to change a serial only the value of row changed in a single column. I have tried Dense_rank, Row_number functions but i could not get the required output. Please help. Sample and required output is as per below.
select entity_no,qty,item_code,
dense_rank()over(partition by entity_no order by entity_no) sr
from
(
select 'DIST/08/0169' entity_no, 14000 qty,'FG00008' item_code from dual
union all
select 'DIST/08/0169' entity_no, 14000 qty,'FG00009' item_code from dual
union all
select 'DIST/08/0169' entity_no, 14000 qty,'FG00010' item_code from dual
union all
select 'DIST/08/0170' entity_no, 10000 qty,'FG00008' item_code from dual
union all
select 'DIST/08/0170' entity_no, 10000 qty,'FG00009' item_code from dual
union all
select 'DIST/08/0170' entity_no, 10000 qty,'FG00010' item_code from dual
union all
select 'DIST/08/0174' entity_no, 12000 qty,'FG00011' item_code from dual
union all
select 'DIST/08/0174' entity_no, 12000 qty,'FG00015' item_code from dual
union all
select 'DIST/08/0174' entity_no, 12000 qty,'FG00010' item_code from dual
)
/
OUTPUT is:
ENTITY_NO QTY ITEM_CO SR
------------ ---------- ------- ----------
DIST/08/0169 14000 FG00008 1
DIST/08/0169 14000 FG00009 1
DIST/08/0169 14000 FG00010 1
DIST/08/0170 10000 FG00008 1
DIST/08/0170 10000 FG00009 1
DIST/08/0170 10000 FG00010 1
DIST/08/0174 12000 FG00011 1
DIST/08/0174 12000 FG00015 1
DIST/08/0174 12000 FG00010 1
9 rows selected.
Required output is
ENTITY_NO QTY ITEM_CO SR
------------ ---------- ------- ----------
DIST/08/0169 14000 FG00008 1
DIST/08/0169 14000 FG00009 1
DIST/08/0169 14000 FG00010 1
DIST/08/0170 10000 FG00008 2
DIST/08/0170 10000 FG00009 2
DIST/08/0170 10000 FG00010 2
DIST/08/0174 12000 FG00011 3
DIST/08/0174 12000 FG00015 3
DIST/08/0174 12000 FG00010 3
9 rows selected.
Thanks for time and support.
] 
Blog
Search
Help
Members
Register
Login
Home
