ROW_NUMBER

The ROW_NUMBER function is an analytic function that assigns a unique number to each row to which it is applied (either each row in a partition or each row returned by the query), in the ordered sequence of rows specified in OrderByClause, beginning with 1.

SQL syntax

ROW_NUMBER () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

ROW_NUMBER has the parameters:

Parameter Description

QueryPartitionClause

See "Analytic Functions" for information on syntax, semantics, and restrictions.

OrderByClause

See "Analytic Functions" for information on syntax, semantics, and restrictions.

Description

  • ROWNUM pseudo column returns a number indicating the order in which TimesTen selects a row from a table or a set of joined rows. In contrast, the analytic function, ROW_NUMBER, gives superior support in ordering the results of a query before assigning the number.

  • By nesting a subquery, using ROW_NUMBER, inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset or rows from the results of the inner query. For consistent results, the query must ensure a deterministic sort order.

  • The return data type is NUMBER.

Example

Use ROW_NUMBER to return the three highest paid employees in each department. Fewer then three rows are returned for departments with fewer than three employees. 

Command> SELECT FIRST 10 department_id, first_name, last_name, salary
         FROM
          (SELECT department_id, first_name, last_name, salary, ROW_NUMBER()
             OVER (PARTITION BY department_id ORDER BY salary desc) rn
           FROM employees )
         WHERE rn <= 3
         ORDER BY department_id, salary DESC, last_name;
< 10, Jennifer, Whalen, 4400 >
< 20, Michael, Hartstein, 13000 >
< 20, Pat, Fay, 6000 >
< 30, Den, Raphaely, 11000 >
< 30, Alexander, Khoo, 3100 >
< 30, Shelli, Baida, 2900 >
< 40, Susan, Mavris, 6500 >
< 50, Adam, Fripp, 8200 >
< 50, Matthew, Weiss, 8000 >
< 50, Payam, Kaufling, 7900 >
10 rows found.