Hi, is it possible to use both LATEST ON and SAMPLE BY in one expression to achive LATEST_ON for each sample? Something like: test table:

ts cat  value
2023-03-03 00:00:00   a      1
2023-03-03 00:00:01   b     10
2023-03-03 00:02:00   a      0
2023-03-03 00:02:01   b     15
2023-03-03 00:04:00   a      1

Some expression:

SELECT
    timestamp, value, cat, count()
FROM test
LATEST ON timestamp PARTITION BY timestamp_sample, cat
SAMPLE BY 1m FILL(PREV);

To get this res:

ts_sample cat  value
2023-03-03 00:00:00   a      1
2023-03-03 00:00:00   b     10

2023-03-03 00:01:00   a      1
2023-03-03 00:01:00   b     10

2023-03-03 00:02:00   a      0
2023-03-03 00:02:00   b     15

2023-03-03 00:03:00   a      0
2023-03-03 00:03:00   b     15

2023-03-03 00:04:00   a      1
2023-03-03 00:04:00   b     15

I don't think latest on can be combined with sample by but how about sample by with group by ?

create table test ( ts timestamp, cat string, value int ) timestamp(ts) partition by day;

insert into test values 
('2023-03-03 00:00:00','a',      1),
('2023-03-03 00:00:01','b',     10),
('2023-03-03 00:02:00','a',      0),
('2023-03-03 00:02:01','b',     15),
('2023-03-03 00:04:00','a',      1);

SELECT   ts, cat, last(value), count()
FROM test
SAMPLE BY 1m  fill(prev)
GROUP BY ts, cat

@Bolek Ziobrowski thank you, but no. Group by takes values in sample range, but I want that it will take all missied values from beyound “the group by range”. Main idea is to get snapshot of the latest values on the each sample ts.

Can you give me an example ? The query I shared does produce the output you requested .

ok, one moment