ROW_NUMBER() OVER () with order by in H2

Question

i'm trying to execute a query on a table in H2 database with ROW_NUMBER clause. Here is my query:

SELECT ROW_NUMBER() OVER (order by data), name FROM students

But i get an error in H2 console:

Syntax error in SQL statement "SELECT ROW_NUMBER() OVER (order[*] by data), name FROM students"; expected ")";

I noticed that it only works if OVER clause is empty like OVER();

Any ideas?

Mcgruder

This is not supported in H2 – Slackjawed

@a_horse_with_no_name: It is (now) :-). See my answer. – Smokejumper

Answer 1

This is not supported in the H2 database before V1.4.198 (release February 2019). You would need to use:

select rownum(), name 
from students 
order by data

As of V1.4.198, support for ROW_NUMBER (and some other window functions) was added (see H2 Changelog), so now your query should work as expected.

Goatfish

I see that you're the creator of H2. Why did you create a new database when there're already good open-source and free stuff like MySQL? – Nolin

There are many reasons, for example MySQL is not written in Java, and it is more complex and bigger. – Goatfish

Hmm, but what exactly is the benefit of being written in Java? (MySQL can run on Windows, Unix, and all major OS.) Usually people don't cite "written in Java" as an advantage, because being binded to the JVM is a disadvantage not an advantage..... – Nolin

For developers that use Java, using a Java database is much simpler than using MySQL (no need to install additional software). It is also much faster, specially the in-memory variant. By the way, you can run H2 on Android as well (even thought SQLite is used there usually). – Goatfish

You are right having to use the JVM is a disadvantage. I was hoping to convert the source code and then use LLVM to compile it at some point. – Goatfish

Actually, most people use H2 either as a unit-test database, or as an embedded database (multi-platform, easier to install than for example MySQL if your application is written in Java). – Goatfish

I ran into bugs in sqllite that made h2 indispensable. I use it for small/ quick up and running db's and testing db's. Mysql is as @ThomasMueller said heavier. – Multiplicity

Can you say when this feature will be implemented? – Prynne

Seems you can not say. – Prynne

@Pavel_K: It's implemented now :-). – Smokejumper

@Smokejumper Thank you for your comment. I will check. – Prynne

Is this implemented for MySQL compatibility mode? Still doesn't seem to work for me on v2.1.214 – Hardesty