ROW_NUMBER [Analytic]

Assigns a sequence of unique numbers, starting from 1, to each row in a window partition. Use the optional window partition clause to group data into partitions before operating on it. For example:

SUM OVER (PARTITION BY col1, col2, ...)

Notes:

  • ROW_NUMBER() is a Vertica extension, not part of the SQL-99 standard.

  • ROW_NUMBER and RANK are generally interchangeable. ROW_NUMBER differs from RANK in that it assigns a unique ordinal number to each row in the ordered set, starting with 1.

Behavior Type

Immutable

Syntax

ROW_NUMBER ( ) OVER ( 
    [ window-partition-clause ] 
    window-order-clause  )

Parameters

OVER()

See Analytic Functions

Examples

The following query partitions customers in the VMart table customer_dimension by occupation. It then ranks those customers according to the ordered set specified by the window partition clause.

=> SELECT occupation, customer_key, customer_since, annual_income,
      ROW_NUMBER() OVER (PARTITION BY occupation) AS customer_since_row_num
   FROM public.customer_dimension
   ORDER BY occupation, customer_since_row_num;

 occupation | customer_key | customer_since | annual_income | customer_since_row_num
------------+--------------+----------------+---------------+------------------------
 Accountant |        49985 | 1987-04-05     |        998685 |                      1
 Accountant |        49977 | 1989-06-14     |        616235 |                      2
 Accountant |        49929 | 1978-10-08     |        298965 |                      3
 Accountant |        49913 | 1988-10-18     |        141013 |                      4
 Accountant |        49844 | 1989-10-04     |        967475 |                      5
 Accountant |        49839 | 1971-11-08     |        942459 |                      6
 Accountant |        49823 | 1979-11-28     |        435959 |                      7
 Accountant |        49817 | 1987-06-15     |        538732 |                      8
 Accountant |        49733 | 1972-04-21     |        651928 |                      9
 Accountant |        49707 | 1980-02-17     |        420219 |                     10
 ...
 Acrobat    |        49912 | 2007-06-08     |        722953 |                      1
 Acrobat    |        49908 | 1996-03-05     |        380288 |                      2
 Acrobat    |        49833 | 2003-11-15     |        317918 |                      3
 Acrobat    |        49770 | 1984-11-18     |        986536 |                      4
 Acrobat    |        49725 | 1973-04-09     |        911064 |                      5
 Acrobat    |        49641 | 1970-11-24     |        870287 |                      6
 Acrobat    |        49605 | 1972-01-08     |        322062 |                      7
 Acrobat    |        49577 | 1980-01-15     |        121727 |                      8
 Acrobat    |        49575 | 1975-11-03     |        835388 |                      9
 ...
 Actor      |        49974 | 2003-06-12     |        885346 |                      1
 Actor      |        49937 | 1986-07-25     |        692557 |                      2
 Actor      |        49889 | 1985-09-09     |        766587 |                      3
 Actor      |        49850 | 1980-01-01     |        328270 |                      4
 Actor      |        49820 | 1984-11-17     |        826061 |                      5
 Actor      |        49780 | 1987-06-01     |         54853 |                      6
 Actor      |        49760 | 2000-07-05     |        255977 |                      7
 Actor      |        49698 | 1971-05-18     |        543584 |                      8
 Actor      |        49676 | 1997-07-23     |        710498 |                      9
 Actor      |        49631 | 1985-11-12     |         67353 |                     10
 ...

See Also