I have this table :
And I would like to make a request that would return for each deal_id
the row with the highest timestamp
, and the corresponding status_id
.
So for this example, I would have returned 2 rows :
1226, 3, 2009-08-18 12:10:25
1227, 2, 2009-08-17 14:31:25
I tried to do it with this query
SELECT deal_id, status_id, max(timestamp) FROM deal_status GROUP BY deal_id
but it would return the wrong status_id
:
1226, 1, 2009-08-18 12:10:25
1227, 1, 2009-08-17 14:31:25
without a single primary key field, I think your best bet is:
select * from deal_status
inner join
(select deal_id as did, max(timestamp) as ts
from deal_status group by deal_id) as ds
on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts
this still won't work if you allow having two different statuses for the same product at the same time
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With