cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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:
  1. 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])
  2. 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
  3. 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
Rate this article:
Version history
Last update:
‎10-16-2021 07:09 AM
Updated by:
Contributors