How to use the SQL ROW_NUMBER function?

Data System Architecture

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.:

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.

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - SQL - Analytic Functions

Analytic functions in the Oracle Database Context An analytic function takes place after that the original data set is retrieved. The clause AVG CORR COVAR_POP COVAR_SAMP...
Data System Architecture
Reporting - Inner N

An inner N reporting retrieve a set of rows in a complete data set classified by its row number. The following inner-N query with the Oracle row_number analytic function selects all rows from the...
Analytic Function Process Order
SQL Function - Window Aggregate (Analytics function)

Windowing functions (known also as analytics) allow to compute: cumulative, moving, and aggregates. They are distinguished from ordinary SQL functions by the presence of an OVER clause. With...
Sqlite Banner
SQLite - Row

A page all row scope subject in Sqlite. Sqlite has an internal rowid The row_number() function window function returns the id of the row ...



Share this page:
Follow us:
Task Runner