Chapter 7 – OLAP Functions

“Don’t count the days, make the days count.”

- Mohammed Ali

The Row_Number Command

SELECT Product_ID, Sale_Date, Daily_Sales,

ROW_NUMBER() OVER

(ORDER BY Product_ID, Sale_Date) AS Seq_Number

FROM   Sales_Table  WHERE Product_ID IN (1000, 2000) ;

image

The ROW_NUMBER Keyword caused Seq_Number to increase sequentially. Notice that this does NOT have a Rows Unbounded Preceding, and it still works!

Quiz – How did the Row_Number Reset?

SELECT Product_ID, Sale_Date, Daily_Sales,

ROW_NUMBER() OVER (PARTITION BY Product_ID

ORDER BY Product_ID, Sale_Date )   AS StartOver

FROM   Sales_Table  WHERE Product_ID IN (1000, 2000) ;

Get Tera-Tom Genius Series - DB2 SQL now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.