javamysqlhsqldbhypersql

How to set Rank function in hypersql database(HSQLDB)

I am switching Database MYSQL to HyperSQL, but i am facing an issue regarding rank functionality. here i'm showing a simple MYSQL query, 

SELECT    first_name,
          age,
          gender,
          @Rank := @Rank + 1 AS rank
FROM      person p, (SELECT @Rank := 0) r
ORDER BY  age;

How to convert it in HyperSQL Query or is there any alternate way to set variable in HyperSQL?

Solution

  • HSQLDB supports an SQL Standard function that provides the same functionality

    SELECT p.*, ROW_NUMBER() over ()
FROM (SELECT first_name,
          age,
          gender
      FROM  person
      ORDER BY  age) p