How to get the latest 3 records of each group from dolphindb database?

Question

My table name is trades, and its columns are permno, symbol, date, prc, shrout, ret, vol. I want to get the latest 3 records of each stock each date group. Does DolphinDB support such querying methods?

Answer 1

    declare @trades as table
    (
    permno int,
    symbol int,
groupdate  date
)
insert into @trades(permno,symbol,groupdate)
values
(1,1,'2019-01-01'),
(2,2,'2019-01-01'),
(3,3,'2019-01-01'),
(4,4,'2019-01-01'),
(1,11,'2019-01-02'),
(2,22,'2019-01-02'),
(3,33,'2019-01-02'),
(4,44,'2019-01-02')

select * from(
select ROW_NUMBER() over(partition by groupdate order by groupdate)as rn,* from @trades)x
where rn <=3

Answer 2

In DolphinDB， one can use context-by clause to solve similar problems. For your question, use the code below:

select * from trades context by symbol, date limit -3

A negative value -3 for limit clause tells the system to get last 3 records for each symbol and date combination.