分组数据并在postgres中显示每组1行

使用 max and sub-query

select T1.name,T1.model_id,r.rev_id,T1.created_at  from      

(
select cm.name,
    cm.model_id,    
    MAX(r.created_at) As created_at from dummy.component cm 
    left join dummy.revision r on cm.model_id=r.model_id 
    group by cm.name,cm.model_id

) T1
left join revision r
  on T1.created_at =r.created_at

http://www.sqlfiddle.com/#!17/68cb5/4

name    model_id    rev_id  created_at
ABC       1234       2           23456
ABC       5678       2           10001
xyz       4567  

在您的选择中,您缺少rev_id

SELECT 
cm.name,
cm.model_id,
MAX(r.rev_id) AS rev_id,
MAX(r.created_at) As created_at
from dummy.component cm 
left join dummy.revision r on cm.model_id=r.model_id 
group by 1,2
ORDER BY cm.name asc,
r.created_at DESC;

您缺少的是一条语句,该语句表示您只需要联接表中的max记录。所以您需要联接记录,但联接将带来表r中的所有记录。如果您按组件中的2列进行分组,然后在id和创建日期上从r中选择最大值,它将只从可加入的列表中选择顶部

我会用 distinct on :

select distinct on (m.id) m.id, m.name, r.rev_id, r.created_at
from model m left join
     revision r
     on m.model_id = r.model_id
order by m.id, r.rev_id;