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.
我想提出一个请求，它将为每个deal_id返回具有最高时间戳的行以及相应的status_id。
So for this example, I would have returned 2 rows :
所以对于这个例子，我会返回2行：
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 :
但它会返回错误的status_id：
1226, 1, 2009-08-18 12:10:25
1227, 1, 2009-08-17 14:31:25
2 个解决方案
#1
15
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
如果您允许同时为同一产品提供两种不同的状态，这仍然不起作用
#2
0
Hi i hope this gives what u want it
嗨，我希望这能给你想要的东西
select deal_id,status_id, timestamp 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 order by deal_id
#1
15
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
如果您允许同时为同一产品提供两种不同的状态，这仍然不起作用
#2
0
Hi i hope this gives what u want it
嗨，我希望这能给你想要的东西
select deal_id,status_id, timestamp 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 order by deal_id