I have a table that contains columns for ticker, date, and price. I would like to retrieve the latest N records for each ticker using SQL, but I haven’t been able to find a solution in the documentation. I know I can do something like this
SELECT * FROM tickers LATEST ON timestamp PARTITION by ticker;
And it will get me the latest row for each ticker value, but I want the latest 3 rows.
We can use a window function for this. I am changing slightly the column names in the query so it can be directly tested on the QuestDB public demo instance at https://demo.questdb.io
with trades_with_pos AS
(select *,
row_number over(partition by symbol order by timestamp desc) as pos
from trades
where timestamp > dateadd('h', -1, now())
)
select * from trades_with_pos where pos <= 3;
The query uses a window function to assign a row number which is partitioned by symbol and order by decreasing timestamp, so the most recent row for each symbol will have a column named pos with value 1, next record for same symbol will have value 2 and so on. Now we only need to add a query around that to select up to whichever limit we need.
Please note since we are doing the order by descending timestamp it can be resource consuming, so I am limiting the search to only the past hour of data.