I have the data like this:
ID TRANS_ID CREATED_DATE STATUS
----------------------------------------------
1 AA 2017-05-19 02:00:00 WAITING
2 AA 2017-05-20 02:00:00 IN_PROCESS
3 BB 2017-05-19 02:00:00 WAITING
4 CC 2017-05-19 02:00:00 WAITING
5 CC 2017-05-20 02:00:00 IN_PROCESS
I would like to show the data in table view like this one :
ID TRANS_ID CREATED_DATE STATUS
----------------------------------------------
2 AA 2017-05-20 02:00:00 IN_PROCESS
3 BB 2017-05-19 02:00:00 WAITING
5 CC 2017-05-20 02:00:00 IN_PROCESS
I take the newest data from each trans_id
and I run this query but it does not work
select id, max(created_date), trans_id, status
from table_a
group by a.transaction_id
One option is to filter with a correlated subquery:
select t.*
from mytable t
where t.created_date = (
select max(t1.created_date) from mytable t1 where t1.trans_id = t.trans_id
)
Alternatively, you can use window functions:
select id, trans_id, created_date, status
from (
select t.*, rank() over(partition by trans_id order by created_date desc) rn
from mytable t
) t
where rn = 1
This allows top ties, if any. If you want no ties, you can use row_number()
instead of rank()
.