Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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?
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.
                There are many reasons, for example MySQL is not written in Java, and it is more complex and bigger.
– Thomas Mueller
                Sep 22, 2015 at 8:43
                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, 2015 at 9:52
                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, 2015 at 13:42
                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, 2015 at 8:20
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.