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.
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