Link to home
Start Free TrialLog in
Avatar of JasonAsh
JasonAsh

asked on

SQL* Plus: Select rownums for each unique transaction

I have the table below in SQL*Plus: 8.0.6.0.0

drop table my_data;

create table my_data
(transaction                         NUMBER(6),
 my_product                        VARCHAR2(6),
 my_product_colour              VARCHAR2(35)
);

insert into my_data
values('12345','BAG','BLACK');
insert into my_data
values('12345','BAG','BLUE');
insert into my_data
values('12345','BAG','BLACK');
insert into my_data
values('12345','SHOES','RED');
insert into my_data
values('15896','BAG','BLACK');
insert into my_data
values('17845','BAG','BLACK');
insert into my_data
values('17845','SHOES','RED');

select * from my_data;

TRANSACTION MY_PRO   COLOUR
----------- ------ -----------------------------------
      12345       BAG         BLACK
      12345       BAG          BLUE
      12345       BAG          BLACK
      12345       SHOES         RED
      15896       BAG          BLACK
      17845       BAG          BLACK
      17845       SHOES          RED

7 rows selected.

What I want to do is select the data above but also with the line number
eg first product in the transaction to be number 1, then 2 etc,
then next transaction 1, 2 etc like below.

TRANSACTION MY_PRO COLOUR            my_row_nums      
----------- ------ -----------------------------------
      12345       BAG          BLACK                1
      12345       BAG          BLUE                                      2
      12345       BAG          BLACK                                    3
      12345       BAG          BLACK                                   4
      15896       BAG          BLACK            1
      17845       BAG         BLACK                                   1
      17845       SHOES          RED                                    2

Thanks,

Jason.
ASKER CERTIFIED SOLUTION
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JasonAsh
JasonAsh

ASKER

Thanks Sujith80 that works perfect.

Thanks for the quick response,

Jason.