Row_Number function

Get Help
1

image
image2212×1512 116 KB
Hi for some reason I am not able to use row_number() in metabase. Can someone help please? Thank you.

2

Hi @jupiter
Does that query work directly on your MySQL server? I’m guessing it’s complaining about some syntax problem, but MySQL is sadly not very helpful with it’s errors.
The error comes from your MySQL database, not Metabase.

3

Hi @flamber,

I ran it on Metabase actually. Does Metabase support row_number() function?

Thanks.

4

@jupiter What you input in the editor is send to your database. What functions it support depends on the database and version, not Metabase.

5

@flamber reviving this discussion. Thanks for your previous insights. I think they make sense. I just started Metabase and wanted to use row_number function on the sample database and its Reviews table. I have not connected to any of my db and just want to check out Metabase.

So when I wrote the below, the SQL ran and produced trivial results.

select ID, PRODUCT_ID, REVIEWER, RATING, row_number() over ()
from REVIEWS

But when I tried to do a little bit more it produced error.

select ID, PRODUCT_ID, REVIEWER, RATING, row_number() over (partition by PRODUCT_ID order by rating)
from REVIEWS

Any thoughts on why this might be the case? Thanks in advance.

6

A simpler reproducible example is below

select ID, PRODUCT_ID, REVIEWER, RATING, row_number() over (order by RATING) as RN
from REVIEWS

The error message has an interesting mention of expected ")";

7

Looks like that the sample database uses H2 engine. And until recently, H2 does not appear to support row_number function. That's what I figured from Google search. Just for anyone that's wondering.

8

@bhjsgmp5 H2 is a very different database compared to Postgres etc. But anything you can do on your database can also be done on Metabase. Connect Metabase to the database type you're using.