I have a table
records which stores
rec_value. A record is stored for each
rec_id only when its
rec_value changes. I would like to query the value for a bunch of
rec_ids at a given time.
For a single
rec_id this query works perfectly fine to query its value at
SELECT rec_time,rec_value FROM records WHERE rec_id = 4 AND rec_time <= 590 ORDER BY time DESC LIMIT 1;
rec_time <= 590 as the latest value can be set at any instance at or before
I would like to extend this to work for multiple
rec_ids in a single query.
SELECT rec_id,rec_time,rec_value FROM records WHERE rec_id IN (4,9,21,565,951,93,6,15,64) AND rec_time <= 590 GROUP BY rec_id ORDER BY time DESC, rec_id ASC;
The query above seems to work but I have noticed that I get inconsistent results and I figured that I'm not using
GROUP BY correctly.
I have tried a few solutions found in slightly different questions here, but I haven't found any solution which gives consistent results yet.
Can this be solved with
GROUP BY or should I use nested queries. If so, how? I gave a shot at using
JOIN but I couldn't get it to work.