postgresql select row with minimum value

Question

This is my query in 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"

And result is:

How to get only one row with minimum "ab" value?

I change this query to:

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"

But get second row with ab = 347.783. What I am doing wrong?

Please tag correctly. Postgres isn't MySQL and isn't Oracle. — Sloan Thrasher
– Sloan Thrasher, Commented Mar 13, 2018 at 6:01
@ Sloan Thrasher, maybe there will be people, who khow the answer — Baurzhan
– Baurzhan, Commented Mar 13, 2018 at 6:03
@SloanThrasher DISTINCT ON has to be Postgres...but maybe the OP wanted a solution for any database. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 13, 2018 at 6:13

Tim Biegeleisen · Accepted Answer · 2018-03-13 06:16:26Z

4

You are missing an ORDER BY clause. You should be ordering first by the id, then by the ab column. Then, Postgres will return a single record for each id corresponding to the lowest ab value.

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;

edited Mar 13, 2018 at 6:16

answered Mar 13, 2018 at 6:05

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Baurzhan Over a year ago

Thanks a lot, you save my day!

Tim Biegeleisen Over a year ago

@Baurzhan Reload the page, I updated my answer. You need to ORDER BY id, ab, if you want to use DISTINCT ON (id).

Bhavesh Ghodasara · Accepted Answer · 2018-03-13 06:07:57Z

0

You can use analytics function as well:

select *
from 
(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,
    rank() OVER (ORDER BY ab )rnk
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) )
where rnk=1

answered Mar 13, 2018 at 6:07

Bhavesh Ghodasara

2,0813 gold badges18 silver badges29 bronze badges

Collectives™ on Stack Overflow

postgresql select row with minimum value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related