About
ROW_NUMBER is an non-deterministic window function (analytic) that returns a sequence of unique numbers.
Usage
With the row number, you can retrieve the following rows and create the follwing reports.:
- bottom-N,
- and inner-N.
Syntax
It has the following syntax:
ROW_NUMBER ( ) OVER ( [partition by myColumn] [order by myOtherColumn])
For consistent results, the row number function should define the sort order.
Example (Oracle)
Without Partition
SELECT channel_desc, calendar_month_desc,
SUM(amount_sold) SALES$,
ROW_NUMBER() OVER (
-- create a sequence with a descendant order in the amount sold
ORDER BY SUM(amount_sold) DESC
) AS ROW_NUMBER
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id
AND times.calendar_month_desc IN ('2001-09', '2001-10')
GROUP BY channel_desc, calendar_month_desc;
Example of results:
CHANNEL_DESC CALENDAR SALES$ ROW_NUMBER
-------------------- -------- -------------- ----------
Direct Sales 2001-10 1,000,000 1
Direct Sales 2001-09 1,100,000 2
Internet 2001-09 500,000 3
Partners 2001-09 600,000 4
Partners 2001-10 600,000 5
Internet 2001-10 700,000 6
With Partition
For each department in the sample table oe.employees, the following example assigns numbers to each row in order of employee's hire date:
SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
. . .
100 Popp 113 6
110 Higgins 205 1
110 Gietz 206 2
ROW_NUMBER is a non-deterministic function. However, employee_id is a unique key, so the results of this application of the function are deterministic.