CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
UUU
UUU

Reputation: 153

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.

Upvotes: 4

Views: 696

Answers (3)

StackSlave
StackSlave

Reputation: 10617

Let me know if this works.

SELECT * FROM trans GROUP BY transno ORDER BY transid DESC LIMIT 1

I'm not a MySQL master, so let me know.

Upvotes: 0

Farid Imranov
Farid Imranov

Reputation: 2085

The best way for this solution is @Prdp's way. But there is another way too. You can use inline view like this:

select * from 
trans t
inner join 
(
   select transno, max(transdatetime) maxtransdatetime from trans group by transno
) s
on s.transno = t.transno and s.maxtransdatetime = t.transdatetime

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 3

Related Questions