Home » SQL & PL/SQL » SQL & PL/SQL » COnditional change Serial (Oracle 11G, Windows 2003)
COnditional change Serial [message #662364] Thu, 27 April 2017 01:21 Go to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
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.
Re: COnditional change Serial [message #662365 is a reply to message #662364] Thu, 27 April 2017 01:30 Go to previous messageGo to next message
Littlefoot
Messages: 21368
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about
dense_rank() over (order by entity_no) sr
Re: COnditional change Serial [message #662366 is a reply to message #662365] Thu, 27 April 2017 02:23 Go to previous message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Thanks. It's resolved.
Previous Topic: NOT LIKE operation on the numbers columns
Next Topic: Need to get the top 3 employees whose salary increased in three consecutive year
Goto Forum:
  


Current Time: Sun Feb 25 16:28:08 CST 2018

Total time taken to generate the page: 0.02306 seconds