使用MAX和GROUP BY选择所有对应的字段 [英] Selecting all corresponding fields using MAX and GROUP BY

问题描述

我有这张表：

我想要为每个 deal_id 返回最高 timestamp ，和相应的 status_id 。

因此，对于这个例子，我会返回2行：

  1226，3，2009-08-18 12： 10:25 
 1227,2，2009-08-17 14:31:25 
  

我试图用这个查询来完成它。

  SELECT deal_id，status_id，max（timestamp）FROM deal_status GROUP BY deal_id 
  

但它会返回错误的 status_id ：

  1226，1，2009-08-18 12:10:25 
 1227,1，2009-08-17 14:31 ：25 
  

解决方案

你最好的选择是：

  select * from dea l_status 
在deal_status.deal_id = ds.did和deal_status中，内部连接
（选择deal_id，max（时间戳）作为ts 
 from deal_status group by deal_id）作为ds 
。 timestamp = ds.ts 
  

如果您允许两个不同的状态产品在同一时间

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

这篇关于使用MAX和GROUP BY选择所有对应的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！