RANK() with NULLS FIRST : Rank « Analytical Functions « Oracle PL / SQL

RANK() with NULLS FIRST

 

SQL> CREATE TABLE all_sales (
  2    year INTEGER,
  3    month INTEGER,
  4    prd_type_id INTEGER,
  5    emp_id INTEGER ,
  6    amount NUMBER(8, 2)
  7  );

Table created.

SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,1    ,1          ,21    ,16034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,2    ,1          ,21    ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,3    ,2          ,21    ,20167.83);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,4    ,2          ,21    ,25056.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,5    ,2          ,21    ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,6    ,1          ,21    ,15564.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,7    ,1          ,21    ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,8    ,1          ,21    ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,9    ,1          ,21    ,19654.57);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,10   ,1          ,21    ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,11   ,1          ,21    ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,12   ,2          ,21    ,10034.64);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,1    ,2          ,22    ,16634.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,1    ,2          ,21    ,26034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,2    ,1          ,21    ,12644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,3    ,1          ,21    ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,4    ,1          ,21    ,25026.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,5    ,1          ,21    ,17212.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,6    ,1          ,21    ,15564.26);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,7    ,2          ,21    ,62654.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,8    ,2          ,21    ,26434.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,9    ,2          ,21    ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,10   ,2          ,21    ,21264.19);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,11   ,1          ,21    ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,12   ,1          ,21    ,10032.64);

1 row created.

SQL>
SQL> select * from all_sales;

      YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT
---------- ---------- ----------- ---------- ----------
      2006          1           1         21   16034.84
      2006          2           1         21   15644.65
      2006          3           2         21   20167.83
      2006          4           2         21   25056.45
      2006          5           2         21
      2006          6           1         21   15564.66
      2006          7           1         21   15644.65
      2006          8           1         21
      2006          9           1         21   19654.57
      2006         10           1         21
      2006         11           1         21   13026.73
      2006         12           2         21   10034.64
      2005          1           2         22   16634.84
      2005          1           2         21   26034.84
      2005          2           1         21   12644.65
      2005          3           1         21
      2005          4           1         21   25026.45
      2005          5           1         21   17212.66
      2005          6           1         21   15564.26
      2005          7           2         21   62654.82
      2005          8           2         21   26434.82
      2005          9           2         21   15644.65
      2005         10           2         21   21264.19
      2005         11           1         21   13026.73
      2005         12           1         21   10032.64

25 rows selected.

SQL>
SQL> --RANK() with NULLS FIRST
SQL>
SQL> SELECT
  2   prd_type_id, SUM(amount),
  3   RANK() OVER (ORDER BY SUM(amount) DESC NULLS FIRST) AS rank
  4  FROM all_sales
  5  GROUP BY prd_type_id
  6  ORDER BY prd_type_id;

PRD_TYPE_ID SUM(AMOUNT)       RANK
----------- ----------- ----------
          1   189077.49          2
          2   223927.08          1

SQL>
SQL> drop table all_sales;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	RANK(): rank items, leaves a gap in the sequence when there is a tie
2.	The ranking as opposed to a row-numbering problem (the problem of ties)
3.	The WHERE is applied before the RANK()
4.	Rank with order by clause
5.	When RANK() is added to the statement, the RANK function is applied last, just before the ordering
6.	RANK() with NULLS LAST demo
7.	Rank(): If the statement requests another ordering, another sort may result
8.	Rank() with null values
9.	Rank() over partition
10.	Browse Products with ranking function
11.	rank() over (partition by deptno order by sal desc )