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','BLAC K');
insert into my_data
values('12345','BAG','BLUE ');
insert into my_data
values('12345','BAG','BLAC K');
insert into my_data
values('12345','SHOES','RE D');
insert into my_data
values('15896','BAG','BLAC K');
insert into my_data
values('17845','BAG','BLAC K');
insert into my_data
values('17845','SHOES','RE D');
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.
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','BLAC
insert into my_data
values('12345','BAG','BLUE
insert into my_data
values('12345','BAG','BLAC
insert into my_data
values('12345','SHOES','RE
insert into my_data
values('15896','BAG','BLAC
insert into my_data
values('17845','BAG','BLAC
insert into my_data
values('17845','SHOES','RE
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the quick response,
Jason.