SQL OLAP

DB2 SQL OLAP : ROW_NUMBER


  • ROW_NUMBER is the most basic OLAP function.
  • It is very easy, powerful, and very handy to use function.
  • ROW_NUMBER assigns a temporary number to each row of result set returned by SQL statement.


The most basic usage of this function looks like following-

SELECT a.* , ROW_NUMBER() OVER() FROM address a

Each row selected from table ‘address’, is assigned a unique sequence number starting with ‘1’ and keeps increasing in the order rows are fetched by SQL statement.

ADDID ADDRESS CITY STATE ZIP COUNTRY ROW_NUMBER
1 9 Wheatstone Rd Madison CT 6443 USA 1
2 316 Drake Cir Iva SC 29655 USA 2
3 13503 Honeytree Ln Pineville NC 28134 USA 3
4 95 Montiero Dre Hinsdale NH 3451 USA 4
5 24939 A Bar Grove Rd Oakboro NH 3451 USA 5
6 120 E Hartley Ave Ridgecrest CA 93555 USA 6
7 400 North Ave Iva SC 29655 USA 7
8 13503 Honeytree Ln Pineville NC 28134 USA 8
22 11223 A Bar Grove Rd Auburn NH 3032 USA 9
42 120 E Hartley Ave Ridgecrest CA 90021 USA 10
342 ABC street NEW DELHI DL 20101 INDIA 11
654 New port drive MUMBAI MH 98762 INDIA 12

Above SQL statement is similar to SQL statement below-

SELECT  a.* , ROW_NUMBER() 
over(order by ADDID, ADDRESS, CITY, STATE, ZIP, COUNTRY ) 
FROM address a

As stated earlier, ROW_NUMBER function assigns the number in the order rows are fetched by SQL statement; so, if fetch sequence of a row changes due to any factor, assigned number also changes for that row. (Refer an example below.)

SELECT  a.* , ROW_NUMBER() over() 
FROM address a ORDER BY a.CITY
ADDID ADDRESS CITY STATE ZIP COUNTRY ROW_NUMBER
22 11223 A Bar Grove Rd Auburn NH 3032 USA 1
4 95 Montiero Dre Hinsdale NH 3451 USA 2
2 316 Drake Cir Iva SC 29655 USA 3
7 400 North Ave Iva SC 29655 USA 4
1 9 Wheatstone Rd Madison CT 6443 USA 5
654 New port drive MUMBAI MH 98762 INDIA 6
342 ABC street NEW DELHI DL 20101 INDIA 7
5 24939 A Bar Grove Rd Oakboro NH 3451 USA 8
3 13503 Honeytree Ln Pineville NC 28134 USA 9
8 13503 Honeytree Ln Pineville NC 28134 USA 10
6 120 E Hartley Ave Ridgecrest CA 93555 USA 11
42 120 E Hartley Ave Ridgecrest CA 90021 USA 12

 

OVER()

It is important to know that ROW_NUMBER() function always needs a function OVER() to work. OVER() provides two options to define how ROW_NUMBER would assign the numbers to the rows in the result set. These options are-

  1. ORDER BY
  2. PARTITION BY

OVER (ORDER BY … )

  1. ORDER BY allows to define the ordering sequence in which the sequence number should be assigned to the rows fetched by SQL.
  2. Always assigns a unique number to each row starting from 1.
  3. This works in the same way as ORDER BY clause works in a simple select statement.
  4. ASC (Ascending) and DESC (Descending) options are allowed.
SELECT a.* , ROW_NUMBER() over(ORDER BY CITY DESC, ZIP) as ROW_NUMBER
 FROM address a order by CITY

NOTE : due to order by CITY clause, display sequence is different than ROW_NUMBER sequence. ROW_ NUMBER is assigned for Descending CITY column while records are being displayed in Ascending CITY column.

ADDID ADDRESS CITY STATE ZIP COUNTRY ROW_NUMBER
22 11223 A Bar Grove Rd Auburn NH 3032 USA 12
4 95 Montiero Dre Hinsdale NH 3451 USA 11
2 316 Drake Cir Iva SC 29655 USA 9
7 400 North Ave Iva SC 29655 USA 10
1 9 Wheatstone Rd Madison CT 6443 USA 8
654 New port drive MUMBAI MH 98762 INDIA 7
342 ABC street NEW DELHI DL 20101 INDIA 6
5 24939 A Bar Grove Rd Oakboro NH 3451 USA 5
3 13503 Honeytree Ln Pineville NC 28134 USA 3
8 13503 Honeytree Ln Pineville NC 28134 USA 4
42 120 E Hartley Ave Ridgecrest CA 90021 USA 1
6 120 E Hartley Ave Ridgecrest CA 93555 USA 2

 

OVER (PARTITION BY … )

  1. PARTITION BY divide the data into virtual partitions and each partition get its own set of rows (and hence ROW_NUMBER sequence).
  2. Every set of ROW_NUMBER sequence starts with 1 (unique within respective partition), so there may be duplicate ROW_NUMBER values in the result set.
SELECT a.* , ROW_NUMBER() OVER(PARTITION BY COUNTRY) as ROW_NUMBER
 FROM address a

NOTE: PARTITION BY COUNTRY create 2 virtual partitions in data; one for COUNTRY = INDIA and other for COUNTRY = USA. Each partition has its own set of ROW_NUMBERs starting with 1.

ADDID ADDRESS CITY STATE ZIP COUNTRY ROW_NUMBER
342 ABC street NEW DELHI DL 20101 INDIA 1
654 New port drive MUMBAI MH 98762 INDIA 2
1 9 Wheatstone Rd Madison CT 6443 USA 1
2 316 Drake Cir Iva SC 29655 USA 2
3 13503 Honeytree Ln Pineville NC 28134 USA 3
4 95 Montiero Dre Hinsdale NH 3451 USA 4
5 24939 A Bar Grove Rd Oakboro NH 3451 USA 5
6 120 E Hartley Ave Ridgecrest CA 93555 USA 6
7 400 North Ave Iva SC 29655 USA 7
8 13503 Honeytree Ln Pineville NC 28134 USA 8
22 11223 A Bar Grove Rd Auburn NH 3032 USA 9
42 120 E Hartley Ave Ridgecrest CA 90021 USA 10

 

OVER (PARTITION BY …  ORDER BY… )

  1. Within each virtual partition created by  “PARTITION BY” , an “ORDER BY” clause can be used to define the order of sequence number generated by ROW_NUMBER() function.
  2. Please be sure that OVER(ORDER BY … PARTITION BY…) is NOT ALLOWED.
SELECT a.* , ROW_NUMBER() OVER(PARTITION BY COUNTRY ORDER BY ZIP DESC) as ROW_NUMBER
 FROM address a

Note : SQL statement first creates partitions by COUNTRY and then order each partition by ZIP in descending order.

ADDID ADDRESS CITY STATE ZIP COUNTRY ROW_NUMBER
654 New port drive MUMBAI MH 98762 INDIA 1
342 ABC street NEW DELHI DL 20101 INDIA 2
6 120 E Hartley Ave Ridgecrest CA 93555 USA 1
42 120 E Hartley Ave Ridgecrest CA 90021 USA 2
2 316 Drake Cir Iva SC 29655 USA 3
7 400 North Ave Iva SC 29655 USA 4
3 13503 Honeytree Ln Pineville NC 28134 USA 5
8 13503 Honeytree Ln Pineville NC 28134 USA 6
1 9 Wheatstone Rd Madison CT 6443 USA 7
4 95 Montiero Dre Hinsdale NH 3451 USA 8
5 24939 A Bar Grove Rd Oakboro NH 3451 USA 9
22 11223 A Bar Grove Rd Auburn NH 3032 USA 10

IBM i developer.

View Comments
There are currently no comments.