UUU UUU - 9 months ago 37
SQL Question

Get latest records of each number in ORACLE

I have set of data in

table. Which contains several transactions of
. I need to get latest transaction record of each
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 Source

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