10

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?

Jose Victor
  • 350
  • 1
  • 5
  • 13

1 Answers1

21

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.

sleske
  • 81,358
  • 34
  • 189
  • 227
Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • 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? – Pacerier Sep 22 '15 at 07:36
  • 7
    There are many reasons, for example MySQL is not written in Java, and it is more complex and bigger. – Thomas Mueller Sep 22 '15 at 08:43
  • 1
    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..... – Pacerier Sep 22 '15 at 09:52
  • 10
    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 Sep 22 '15 at 13:42
  • 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 Sep 23 '15 at 05:55
  • 12
    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). – Thomas Mueller Sep 25 '15 at 08:20
  • 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. – WestCoastProjects Feb 11 '17 at 07:43
  • Can you say when this feature will be implemented? – Pavel_K Apr 04 '17 at 11:35
  • 1
    Seems you can not say. – Pavel_K Sep 24 '18 at 08:16
  • @Pavel_K: It's implemented now :-). – sleske Aug 28 '19 at 10:19
  • @sleske Thank you for your comment. I will check. – Pavel_K Aug 28 '19 at 10:38