ROW_NUMBER() OVER () with order by in H2

database h2 row-number
Jose Victor picture Jose Victor · Sep 2, 2015 · Viewed 9.9k times · Source

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

Thomas Mueller picture Thomas Mueller · Sep 3, 2015

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.

Related questions

H2 in-memory database. Table not found

I've got a H2 database with URL "jdbc:h2:test". I create a table using CREATE TABLE PERSON (ID INT PRIMARY KEY, FIRSTNAME VARCHAR(64), LASTNAME VARCHAR(64));. I then select everything from this (empty) table using SELECT * FROM PERSON. So far, …

Read More
Frontend tool to manage H2 database

How to use H2 database's integrated managment frontend? For operations such as create table, alter table, add column, and so on.

Read More
Can I have H2 autocreate a schema in an in-memory database?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.) I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting …

Read More