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.
I ran it on Metabase actually. Does Metabase support row_number() function?
@jupiter What you input in the editor is send to your database. What functions it support depends on the database and version, not Metabase.
@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 ()
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)
Any thoughts on why this might be the case? Thanks in advance.
A simpler reproducible example is below
select ID, PRODUCT_ID, REVIEWER, RATING, row_number() over (order by RATING) as RN
The error message has an interesting mention of expected ")";
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.
@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.