1

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.

3
  • Do you need one record per different code_id and per different rec_id? You're grouping but not applying grouping or functions to the selected results. Adding a table and some sample data + expected result could help getting to an answer faster. – Randi Vertongen Mar 12 '19 at 10:05
  • Hi, let me edit the question. I was converting my table to more meaningful names and hastily made some mistakes. code_id = rec_id. Sorry about that! I need 1 record for each rec_id which gives me the rec_value for a given rec_time. – srik Mar 12 '19 at 10:07
  • No problem! I think that you need to either add rec_time,rec_value to the group by, or apply a function to it like MAX() or MIN() – Randi Vertongen Mar 12 '19 at 10:30
1
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;
5
  • Hi do you know if this works for SQLite? I am getting some error at '('. I will look into it and update here if it works or not. Thanks – srik Mar 12 '19 at 13:43
  • Hi do you know if this works for SQLite? Check it using some online fiddle (create tables, insert sample data, then test the queries). On dbfiddle.uk, for example... – Akina Mar 13 '19 at 5:23
  • What I meant to ask was if you are aware about SQLite supporting PARTITION and CTE, terms which I hadn't come across before. Seems like PARTITION isn't supported but WITH is supported. I'll try to get that working and update here. Thanks – srik Mar 13 '19 at 12:45
  • 1
    @srik "Built-in window functions honor any PARTITION BY clause in the same way as aggregate window functions - each selected row is assigned to a partition and each partition is processed separately." SQLite - Window functions. Maybe SQLite may use separate WINDOW clause only - check, and, if true, edit. – Akina Mar 13 '19 at 13:27
  • Thank you for the clarification. I'll look into it. – srik Mar 15 '19 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.