SQL Analytics 101 - Row_Number()

Here is a simple example for when SQL Analytical Functions are simple yet useful.

I wanted a basic 1,2,3,4 count so I could alternate colours in a report.

select ename, sal, rownum rn
  ,mod(rownum,4) mod_rn
  ,mod(row_number() over (order by sal),4) mod_rna
from emp
order by sal

ENAME      SAL     RN     MOD_RN  MOD_RNA 
---------- ------- ------ ------- --------
SMITH      800     7      3       1       
JAMES      950     13     1       2       
ADAMS      1100    12     0       3       
MARTIN     1250    10     2       0       
WARD       1250    9      1       1       
MILLER     1300    14     2       2       
TURNER     1500    11     3       3       
ALLEN      1600    8      0       0       
CLARK      2450    3      3       1       
BLAKE      2850    2      2       2       
JONES      2975    4      0       3       
FORD       3000    6      2       0       
SCOTT      3000    5      1       1       
KING       5000    1      1       2       

 14 rows selected 

Column "MOD_RN", based on ROWNUM, is unpredictable due to the order by.

Replace ROWNUM with ROW_NUMBER(), which mimicks the ORDER BY for the statement, and we return to predictability.

ROW_NUMBER() is one of my favourite and most frequently used analytical functions. I've used it for basic numbering devices such as this, identifying duplicates, compressing sequences, APEX checkbox values, defining precedence within an in-line view, and probably much more.

Scott