• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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.
0
JasonAsh
Asked:
JasonAsh
1 Solution
 
SujithData ArchitectCommented:
select TRANSACTION , MY_PROduct , my_product_COLOUR , row_number() over( partition by transaction order by MY_PROduct ) my_row_nums      
from my_data
/
0
 
JasonAshAuthor Commented:
Thanks Sujith80 that works perfect.

Thanks for the quick response,

Jason.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now