Reputation: 215
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
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