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