Closest value within tolerance band for multiple ids

Question

I'm looking for something that, given a table like:

| channelid  | curveid  | xvalue   |
| 1          | 21       | 179.9216 |
| 1          | 21       | 180.4314 |
| 1          | 21       | 180.6528 |
| 1          | 21       | 180.9251 |
| 1          | 21       | 181.1334 |
| 1          | 21       | 181.4417 |
| 2          | 21       | 179.9513 |
| 2          | 21       | 180.1612 |
| 2          | 21       | 180.2022 |
| 2          | 21       | 180.8762 |
| 2          | 21       | 181.1331 |
| 2          | 21       | 181.2842 |
| 1          | 22       | 179.9213 |
| 1          | 22       | 180.4415 |
| 1          | 22       | 180.6226 |
| 1          | 22       | 180.9758 |
| 1          | 22       | 181.1639 |
| 1          | 22       | 181.4212 |
| 2          | 22       | 179.9715 |
| 2          | 22       | 180.1513 |
| 2          | 22       | 180.2326 |
| 2          | 22       | 180.8265 |
| 2          | 22       | 181.1437 |
| 2          | 22       | 181.2442 |

returns me the closest xvalue to a value found for each unique channelid curveid combination.

I found this and this. With this I can find the closest value in total. However I need help extending it so it returns the closest to each unique combination.

SELECT * FROM table 
WHERE xvalue >= ($myvalue - .5) AND xvalue <= ($myvalue + .5) 
ORDER by abs(xvalue - $myvalue)

Thanks!

score 1 · Answer 1 · answered Sep 02 '18 at 11:56

You could use a group by

select channelid, curveid,min(abs(xvalue - $myvalue)) 
from table
WHERE xvalue >= ($myvalue + .5) AND xvalue <= ($myvalue - .5) 
group by channelid, curveid 
ORDER by   min(abs(xvalue - $myvalue)) asc

and if you need also the related xvalue

select table.*, t.min_diff  from table  
inner join  (

  select channelid, curveid,min(abs(xvalue - $myvalue))  min_diff
  from table  
  WHERE xvalue >= ($myvalue + .5) AND xvalue <= ($myvalue - .5) 
  group by channelid, curveid 
  ORDER by   min(abs(xvalue - $myvalue)) asc 

) t on t.channelid = table.channelid 
      and y.curveid = table.curveid 
        and abs(table.xvalue - $myvalue) = t.min_diff

score 1 · Accepted Answer · answered Sep 02 '18 at 16:04

The best way to do this in Postgres uses distinct on:

SELECT DISTINCT ON (channelid, curveid) t.*
FROM table  
ORDER by channelid, curveid, abs(xvalue - $myvalue);

If you know the nearest value is within 0.5 of $myvalue for all combinations you want, then you can add your where clause.

Joachim Nielandt · Answer 3 · 2018-09-02T13:07:35.747

I like using with to first prepare my data.

with min as
   (
     select channelid, curveid, min(abs(xvalue-$myvalue)) mindifference 
     from table group by channelid, curveid
   )
select channelid, curveid, xvalue from min innner join table using (channelid, curveid) where abs(xvalue-$myvalue) = mindifference

This has the advantage to see ex-aequo results, and you can easily access the minimum difference in your outer query.

Closest value within tolerance band for multiple ids

3 Answers3