How To Filter For The Last Two Records Using SQL In Ecube
Question
How to find out if there exists the top 2 records for the same ClientID in a table. I know that the MAX gives the Top record and that SQL has the LIMIT 2 but I cannot use these two keywords in eCube.
Answer
This can be easily done within the ElastiCube. Here are the steps:
- Create a new custom column in your table to ranks all records for a specific ClientID, the script will be something like this: rankasc([client_id],[created_at])
- Create a custom SQL expression (and name this table [Table Max Ranking]) to find out the max ranking for each client, the syntax will be :
SELECT client_id, max([rank]) AS MaxRank
FROM [Table]
GROUP BY client_id - Create another custome SQL expression with the following scripts :
SELECT a.*
FROM [Table] a
JOIN [Table Max Ranking] b ON a.client_id = b.client_id AND a.[rank] >= (b.MaxRank - 1)
ORDER BY a.client_id, a.[rank] desc
Published 10-16-2021
