1

MariaDB/MySQL without window function support.

Table DEVICE {
 id int,   <— Primary key
 name  varchar(10)  
}

For every device there are many records in DATA table

Table DATA (
  device_id   int,    <— Foreign key to DEVICE.id
  value float
)

User provides some target_value.

Task: find the closest values in DATA table for every device.

DEVICE
-----------
id |  name 
-----------
1  |  A
2  |  B
3  |  C


       DATA
------------------------
device_id      |  value 
------------------------
 1             |  5.3
 1             |  5.9
 2             |  4.0
 2             |  4.5
 3             |  5.1

For the input target_value=5.0 the expected output is:

--------------------
id |  value 
---------------------
 1 |  5.3
 2 |  4.5
 3 |  5.1

I know how to find closest value for a single device (e.g. device_id=2):

SELECT device_id, value FROM DATA 
WHERE device_id=2 ORDER BY ABS(value-target_value) ASC LIMIT 1

... but I do not know how to extend it to return results for several device_id's

user510040
  • 159
  • 2
  • 10
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Aug 08 '17 at 06:54
  • SELECT d.device_id, d.`value` FROM `DATA` d JOIN device dev on dev.id = d.device_id WHERE ROUND(ABS(d.`value`-5.0)) = 0 ORDER BY d.device_id; – Лев Макаренко Aug 08 '17 at 07:42
  • This answer is wrong. More specifically, this part is not correct: ROUND(ABS(d.value-5.0)) = 0 because d.value is not guaranteed to be in range 4.0-6.0 – user510040 Aug 08 '17 at 16:20

0 Answers0