ROW_NUMBER

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the number of the current row within its partition, counting from 1.

1row_number() OVER ( [ PARTITION BY <expression> [, ...] ] [ ORDER BY <expression> [, ...] ] )

For the full OVER clause syntax, including frame clauses and the WINDOW clause, see Window Function Call Syntax and WINDOW Clause.

row_number takes no arguments.

Returns a bigint.

• All window functions depend on the sort ordering specified by ORDER BY in the OVER clause. If you omit ORDER BY, rows are processed in an unspecified order, so the row numbers assigned are also unspecified.
• Unlike rank and dense_rank, row_number doesn’t account for peer groups. Every row receives a distinct, sequential number even when rows are tied on the ORDER BY expression.

Assign a row number to each employee within their department, ordered by salary from highest to lowest.

1SELECT depname, empno, salary, row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
2FROM empsalary;

Omit PARTITION BY to number every row in the result set as a single partition.

1SELECT empno, salary, row_number() OVER (ORDER BY salary DESC)
2FROM empsalary;

• RANK
• DENSE_RANK
• Aggregate Functions
• Window Functions