Rajesh
Rajesh

Reputation: 215

Looking for close value to zero for a particular id

I'm looking for the nearest value to zero for a particular id

My input is

    tech_Id time_diff
    1000      100
    1000      200
    1000      -50
    1000      -10
    1001      100
    1001      200
    1001       50
    1001       10
    1002      -50
    1002      -10
    1003      130
    1003     -140
    1003      -2

expected output

tech_Id time_diff
1000     -10
1001      10
1002     -10
1003      -2

thanks in advance

Upvotes: 1

Views: 34

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

You can find min of absolute value of time diff.

select t1.*
from your_table t1
join (
    select tech_id, min(abs(time_diff)) as time_diff
    from your_table
    group by tech_id
    ) t2 on t1.tech_id = t2.tech_id and abs(t1.time_diff) = t2.time_diff;

Upvotes: 2

Related Questions