Title
b
Bartosz
12/01/2022, 9:44 AMHello, quys. I've table with address which is type SYMBOL. At this moment, to select last know value before some timestamp of each address i'm using LIMIT -1 and glue results with UNION. Is there some better solution to query without subqueries to make this?
SELECT * FROM (
SELECT address, value, isFloat, created_at, ts
FROM module_history
WHERE
devid = 'YKRLSRGWXB' AND
address = 'P30u12' AND
ts < '2022-12-01T06:37:00.000Z'
LIMIT -1
) UNION (SELECT address, value, isFloat, created_at, ts
FROM module_history
WHERE
devid = 'YKRLSRGWXB' AND
address = 'P30v12' AND
ts < '2022-12-01T06:37:00.000Z'
LIMIT -1 )j
javier ramirez
12/01/2022, 9:56 AMThis is a very similar case to the query example we have at the demo site. You can run this query at https://demo.questdb.io
SELECT * FROM trades
WHERE symbol in ('BTC-USD', 'ETH-USD')
LATEST ON timestamp PARTITION BY symbol;SELECT address, value, isFloat, created_at, ts
FROM module_history
WHERE
devid IN ('YKRLSRGWXB', 'YKRLSRGWXB') AND
address IN ('P30u12', 'P30v12')
LATEST ON ts PARTITION BY devid, addressI think something like that for your query
b
Bartosz
12/01/2022, 9:58 AMWow, thank you. Before i did some mistake because performance was terrible. Thank you!
j
javier ramirez
12/01/2022, 9:58 AMTry and let us know if it performs well 🙂
b
Bartosz
12/01/2022, 9:58 AMIt is excellent 🙂
j
javier ramirez
12/01/2022, 9:59 AMyay!