UUU UUU - 1 month ago 5
SQL Question

Get latest records of each number in ORACLE

I have set of data in

Trans
table. Which contains several transactions of
transno
. I need to get latest transaction record of each
transno
.
These data stored in Oracle database.

enter image description here

I have tried below query, with few changes in every time. But i gives only one raw. This table contain more than 1m records.

select * from (select transid,transno,transdate,transtype
from trans order by TRANSID desc) where rownum <= 1


Please help on this.

Answer

You need to use ROW_NUMBER window function to get the latest transdate for all the transno

select * from 
(
select  transid,transno,transdate,transtype,
Row_number()over(partition by transno order by transdate desc) as rn
from trans
) where RN = 1