postgresql 选择具有最小值的行

Question

这是我在 PostgreSQL 中的查询:

SELECT 
    "axapta_calls".id, 
    "axapta_calls".call_time, 
    calls.calltime, 
    "calls"."id" as "call_id",  
    abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp))) as ab 
FROM 
    "axapta_calls" 
inner join 
    "calls" (ON 
        axapta_calls.converted_outer_phone=calls.caller_phone
        and abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp)))<= 600 )
WHERE  ("axapta_calls"."id" > 0) 
GROUP BY "axapta_calls"."id", "calls"."id" 

结果是:

如何只获得最小“ab”值的一行？

我将此查询更改为:

SELECT 
    distinct on (axapta_calls.id)
    "axapta_calls".id, 
    "axapta_calls".call_time, 
    calls.calltime, 
    "calls"."id" as "call_id",  
    abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp))) as ab 
FROM 
    "axapta_calls" 
inner join 
    "calls" ON 
        axapta_calls.converted_outer_phone=calls.caller_phone
        and abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp)))<= 600 
WHERE  ("axapta_calls"."id" > 0) 
GROUP BY "axapta_calls"."id", "calls"."id" 

但是得到第二行 ab = 347.783。我做错了什么？

Answer 1

您缺少一个 ORDER BY 子句。您应该首先按 id 排序，然后按 ab 列排序。然后，Postgres 将为与最低 ab 值对应的每个 id 返回一条记录。

SELECT 
    distinct on (axapta_calls.id),
    "axapta_calls".id, 
    "axapta_calls".call_time, 
    calls.calltime, 
    "calls"."id" as "call_id",  
    abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp))) as ab 
FROM 
    "axapta_calls" 
inner join "calls"
    ON axapta_calls.converted_outer_phone=calls.caller_phone and
       abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp)))<= 600 )
WHERE  ("axapta_calls"."id" > 0)
ORDER BY
    axapta_calls.id, 5;