Hi is something like quasarDb s RESTRICT TO operator possibl

Question

Hi is something like quasarDb s RESTRICT TO operator possible in QuestDb It seems somewhat similar to LATEST ON but latest on can t have a time stamp as its PARTITION BY We are trying to meet the use case described here <https doc quasar ai master user guide howto forecast html|https doc quasar ai master user guide howto forecast html>

Jaromir Hamala · Answer

hi Lars this looks like an omission in questdb I see no good reason why `latest on` could not partition by `timestamp` given it can partition by `long` I ll explore this further and will let you know

Jaromir Hamala · Answer

here is a possible workaround consider this schema `create table forecasts when timestamp version timestamp temperature double timestamp version partition by day ` Data in the table are sorted by the version =when the forecast was created then insert 6 forecasts 3 versions for each days ```insert into forecasts values now dateadd h 10 now 40 now dateadd h 9 now 30 now dateadd h 1 now 42 insert into forecasts values dateadd d 1 now dateadd h 10 now 140 dateadd d 1 now dateadd h 9 now 140 dateadd d 1 now dateadd h 1 now 142 ``` ideally you would like to do this ```select from forecasts latest on version PARTITION by when ``` but this does not work due to partitioning by a timestamp not being supported so this is a possible workaround ``` select cast when as long as when long from forecasts latest on version PARTITION by when long ``` the subquery casts the `timestamp` to `long` and then it s used for partitioning that being said it s still a workaround I will explore why partitioning by timestamp is not implemented and will add it if that s just an omission and one more warning bear in mind `latest on` in most efficient when partitioning by a symbol because questdb knows all symbols currently present in a table and it can short circuit full table scanning when partitioning by `long` it cannot use this optimization

javier ramirez · Answer

I believe you should be able to do this today using row number over partition by timestamp order by version DESC then filtering only rows with number 1

javier ramirez · Answer

it would be slower than LATEST ON but should produce accurate results

javier ramirez · Answer

using the table Jaromir showed above you can do

javier ramirez · Answer

```with numbered AS select row number OVER partition by `when` order by `version` desc as r num from forecasts select from numbered where r num = 1```

javier ramirez · Answer

and get correct results

javier ramirez · Answer

try executing without the where if you want to see the individual ranked results

Lars Riis Olsen · Answer

Thanks I will give it a try

Vlad · Answer

another workaround is to cast timestamp to `long` and have only one date in the table to save on storage For example this query can execute on <http demo questdb io|demo questdb io> and does what you re looking for ```select timestamp tt timestamp symbol side price amount from SELECT timestamp date trunc day timestamp long tt symbol side price amount FROM trades WHERE symbol in BTC USD ETH USD and timestamp gt dateadd d 10 now LATEST ON timestamp PARTITION BY symbol tt side ```