How to correctly use group by for getting the record with the closest time for each rec_id

greatest-n-per-groupgroup bysqlite

Background

I have a table records which stores rec_id, rec_time and 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 rec_time 590:

SELECT rec_time,rec_value 
FROM records 
WHERE rec_id = 4 AND rec_time <= 590 
ORDER BY time DESC 
LIMIT 1;

I use rec_time <= 590 as the latest value can be set at any instance at or before 590.

PROBLEM

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.

Best Answer

WITH cte AS ( SELECT rec_id, 
                     rec_time, 
                     FIRST_VALUE(rec_value) OVER (PARTITION BY rec_id ORDER BY rec_time DESC) rec_value
              FROM records 
              WHERE rec_id IN (4,9,21,565,951,93,6,15,64) 
              AND rec_time <= 590 )
SELECT rec_id, MAX(rec_time) rec_time, rec_value
FROM cte 
GROUP BY rec_id /* , rec_value */ ;

or ever

SELECT DISTINCT rec_id, 
                MAX(rec_time) OVER (PARTITION BY rec_id) rec_time, 
                FIRST_VALUE(rec_value) OVER (PARTITION BY rec_id ORDER BY rec_time DESC) rec_value
FROM records 
WHERE rec_id IN (4,9,21,565,951,93,6,15,64) 
AND rec_time <= 590;
Related Question