Rank Over() Alternative in Metabase (Rank function in Metabase)

While writing SQL queries, sometimes we need to use RANK function with GROUP BY.

But the current version of Metabase doesn't support it.

So, here is the alternative of RANK function.


Suppose you have a table which consists of User IDs and Order Date. There may be multiple orders by a customer. So, there will be multiple Order Dates for a single User ID.

Sample Input -

user_id order_date
1 '2022-07-15'
2 '2022-07-18'
2 '2022-06-13'
1 '2022-08-25'
2 '2022-10-12'

Now, suppose you want to give a rank to every order for every customer based on the order date.


user_id order_date order_number
1 '2022-07-15' 1
2 '2022-07-18' 2
2 '2022-06-13' 1
1 '2022-08-25' 2
2 '2022-10-12' 3

To do this, MySQL has this simple syntax :

SELECT user_id, order_date, rank()
OVER ( partition by user_id order by order_date ) AS 'order_number' FROM tblorders

But the current version of Metabase doesn't support this.

So, you can use this syntax :

SELECT user_id, order_date,
(SELECT COUNT(*) FROM tblorders tod2 WHERE tod1.user_id = tod2.user_id AND
tod1.user_id >= tod2.user_id) AS 'order_number'
FROM tblorders tod1

It will return the desired result.

You can sort on basis of user_id as per your requirement.

Thanks !!

Hi @kojaram
When you write SQL, then the query is sent to your database, so if it doesn't work, then it's not supported by your database (it's nothing to do with Metabase or its version).
If I remember correctly, MySQL 8+ is required for windowing functions.