Get the latest entries for every ORACLE issue

Question

I have a dataset in a table Trans. Which contains several transactions transno. I need to get the latest transaction record for each transno. This data is stored in an Oracle database.

I tried the following query, with slight changes each time. But I give only one raw material. This table contains more than 1 m records.

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

Please help us with this.

Answer 1

You need to use the window function ROW_NUMBERto get the last one transdatefor everyone.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

Answer 2

Let me know if this works.

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

I am not a MySQL master, so let me know.

Answer 3

The best way for this solution is the @Prdp method. But there is another way. You can use inline viewas follows:

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