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?

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.