RANK ():- As the function name implies, RANK returns ranking (ordered) of rows based on the number or expression given in the ORDER BY clause. Note that we do not need to give anything inside the RANK function. Just give it to the ORDER BY clause and things would be taken care. Let’s work out a scenario.

Scenario is to find out the sales figure for each store based on their actual sales.

SELECT store_no, sale_month, actual_sale,

RANK () OVER (PARTITION BY store_no ORDER BY actual_sale) as SaleRank

FROM sales;

Note that there is nothing being fed to the function RANK. Result set below shows the sales figure ranked, for each store.

      store_no  sale_month    actual_sale    SaleRank

          10      05/01/2007    13,500           1

          10      01/01/2007    13,500           1         

          20      02/01/2007    11,000           1

          20      04/01/2007    12,500           2

          30      02/01/2007    10,000           1

          30      03/01/2007    10,500           2

          30      03/01/2007    12,500           3

          40      02/01/2007    11,500           1

          40      04/01/2007    12,500           2

          50      01/01/2007    10,500           1

          50      05/01/2007    12,500           2

          60      06/01/2007    11,500           1

          70      07/01/2007    11,500           1

          80      07/01/2007    15,500           1

          90      06/01/2007    15,500           1

ROW_NUMBER ():-  It returns the sequential number of the row within a group, starting with 1 based on the ORDER BY clause. With the above scenario if we replace the RANK with ROW_NUMBER and run the following query:

SELECT store_no, sale_month, actual_sale,

ROW_NUMBER() OVER (PARTITION BY store_no ORDER BY actual_sale) as SaleRank

FROM sales;

The result set we get is similar, only difference is for first two rows we have 1,1 with RANK but 1,2 with ROW_NUMBER. This is because ROW_NUMBER takes into consideration the sequential occurrence of the row in a group which is store_no here.

store_no   sale_month    actual_sale    SaleRank

10            05/01/2007    13,500           1         

10            01/01/2007    13,500           2                 

20            02/01/2007    11,000           1                 

20            04/01/2007    12,500           2         

30            02/01/2007    10,000           1         

30            03/01/2007    10,500           2         

30            03/01/2007    12,500           3         

40            02/01/2007    11,500           1         

40            04/01/2007    12,500           2         

50            01/01/2007    10,500           1         

50            05/01/2007    12,500           2         

60            06/01/2007    11,500           1         

70            07/01/2007    11,500           1         

80            07/01/2007    15,500           1         

90            06/01/2007    15,500           1

Popularity: 31% [?]

Our Random Articles

• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress
• Usage of Macro in Teradata

More Links