Skip to main content

rownum

1. ROWNUM Function​

The ROWNUM function in H2 database is used to generate a unique sequential number for each row returned by a query.

2. Syntax​

The syntax for the ROWNUM function in H2 database is as follows:

ROWNUM()

Arguments​

  • The ROWNUM function does not take any arguments.

Return​

  • The ROWNUM function returns a unique sequential number for each row.

3. Notes​

  • The ROWNUM function is usually used in combination with the ORDER BY clause to generate sequential numbers based on a specific order.
  • The sequential numbers generated by ROWNUM start from 1 and increment by 1 for each row.
  • ROWNUM is a pseudo-column, meaning it is not an actual column in the table, but rather a value generated by the database during query execution.
  • ROWNUM is always calculated based on the order in which the rows are returned by the query, and not based on the physical order of the rows in the table.

4. Examples​

Here are a few examples demonstrating the usage of the ROWNUM function in H2 database:

Example 1 - Generating sequential numbers for all rows:​

SELECT ROWNUM(), first_name, last_name
FROM employees;

Output:

ROWNUM() | first_name | last_name
---------+------------+-----------
1        | John       | Doe
2        | Jane       | Smith
3        | Mark       | Johnson

Example 2 - Generating sequential numbers based on a specific order:​

SELECT ROWNUM(), product_name, price
FROM products
ORDER BY price DESC;

Output:

ROWNUM() | product_name  | price
---------+---------------+-------
1        | Laptop        | 1500
2        | Smartphone    | 1000
3        | Smart TV      | 800
4        | Headphones    | 200
  • None. The ROWNUM function is specific to the H2 database and may not be available in other databases.