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?

Solution

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.

Comments

[Pacerier]

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?

[Thomas Mueller]

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

[Pacerier]

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.....

[Thomas Mueller]

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).

[Thomas Mueller]

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.

[Thomas Mueller]

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).

[WestCoastProjects]

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.

[Pavel_K]

Can you say when this feature will be implemented?

[Pavel_K]

Seems you can not say.

[sleske]

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

[Pavel_K]

@sleske Thank you for your comment. I will check.