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
andRANK
are generally interchangeable.ROW_NUMBER
differs fromRANK
in that it assigns a unique ordinal number to each row in the ordered set, starting with 1.
Behavior Type
Syntax
ROW_NUMBER ( ) OVER ( ... [ window-partition-clause ] ... window-order-clause )
Parameters
OVER()
|
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 ...