DWQA QuestionsCategory: DatabaseIs contxt by + last the last record after each group
Robin asked 1 month ago

If context by is used to group in dolphin DB database, there will be multiple records. I want to get the last one in each group. The code is as follows:

select last(ShortTermLoan),InfoPublDate,CompanyCode,EndDate,IfAdjusted,IfMerged 
from tbFactor
where EndDate = 2015.06.30,IfAdjusted == 2 and IfMerged == 1 and CompanyCode == 1518 
context by InfoPublDate,CompanyCode,EndDate,IfAdjusted,IfMerged 

However, multiple records are returned, as shown in the figure below,
contextbylast_20200613093622.png
My problem is that after context by grouping, each group should have multiple records, and then I take last. In this way, isn’t the last record in each group returned?

2 Answers
wale answered 1 month ago

Last is an aggregate function. The number of records returned by context by is the same as the length of the group, so multiple records will be generated repeatedly.
Context by can use top 1 to get the last record. The code is as follows:

select top 1 last(date(EndDate)) as date,last(ShortTermLoan) as v from tbFactor
where EndDate >= 2015.06.30,EndDate <= 2020.03.31,IfAdjusted == 2 and IfMerged == 1 and CompanyCode == 1518 
context by InfoPublDate,CompanyCode,EndDate,IfAdjusted,IfMerged csort JSID asc
Davis answered 1 month ago

If only the last record is retrieved, there is no need to use the aggregate function last. Instead, use the limit clause directly. If the value after limit is negative, it means the last few records, and the positive number is the previous records. When limit and context by are used together, the output of each group is limited, not the overall result.

select date(EndDate) as date,ShortTermLoan as v from tbFactor
where EndDate >= 2015.06.30,EndDate <= 2020.03.31,IfAdjusted == 2 and IfMerged == 1 and CompanyCode == 1518 
context by InfoPublDate,CompanyCode,EndDate,IfAdjusted,IfMerged csort JSID asc, limit -1