CODEWITHSUNDEEP
oracle-database
Sally El-Sayed
Sally El-Sayed

Reputation: 161

Getting the first occurrence of group of rows

Am having a case where I have transaction and this transaction consists of several steps. I want to return the first occurrence of this transaction, for example: 

Trn Loc step

111 0   1

111 0   2

111 0   3

222 3   1

222 3   2

333 5   1

333 5   2

333 5   3

and i want to get this result :

tran                 loc

111                  0    

222                  3   

333                  5

I think it is supposed to be done by partition function but I don't know how...any help please?

Upvotes: 0

Views: 7059

Answers (1)

Zohaib
Zohaib

Reputation: 7116

select t.trn, t.loc 
from (select trn, loc, ROW_NUMBER() OVER (PARTITION BY trn, loc ORDER BY trn, loc) as rnum
from table ) t
where t.rnum = 1

Or you can use RANK() function instead of ROW_NUMBER(), rest of syntax is same. http://www.techonthenet.com/oracle/functions/rank.php

Upvotes: 3

Related Questions