SqlTutorial-1 : Lesson-11 : Class-6 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Built-in Sql functions: Ranking functions

INDEX

Ranking functions

1. RANK()
2. DENSE_RANK()
3. STDEV
4. VAR
5. VARP
6. ROW_NUMBER

1. Ranking functions

1. RANK():

   Finds rank of for given column. It increments each row by 1 but skips sub-sequent increments for duplicates.

   Syntax:

   RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
   If repetitive values exist, the same rank would be assigned to all of them.
   For each duplicate value, the RANK() function skips the subsequent value so that the next non-duplicate value remains in its rightful position.

   Examples:

   1. You can rank Integer type of column values.

      SELECT RANK() OVER(ORDER BY budget DESC)
      FROM department
      WHERE budget <= 50000

   2. You can rank any kind of data values. The following is varchar.

      Select RANK() over(Order by region desc) as Rank, region from employee
       Rank              Region
      ------------       --------------
       1                     West
       1                     West
       3                     South
       3                     South
       5                     North
       5                     North
       5                     North
       8                     East
       8                     East
   
   

2. DENSE_RANK():

   Finds rank of for given column with subsequent increment even for duplicates.
   The main difference between RANK() and DENSE_RANK() is that for duplicates(repetitive) values, RANK() skips sub-sequent rank value
   whereas DENSE_RANK() does not skips and continues with next sub-sequent rank.

   Exampels:

   RANK()

   Select RANK() over(Order by salary desc),salary from employee
   Rank         Salary
   -----          -----------
     1           6678.00
     2           6456.00
     3           6123.00
     4           5890.00
     5           5567.00
     6           5567.00
     7           5345.00
     8           4789.00
     9           4234.00
   
   

   
   DENSE_RANK()

   Select DENSE_RANK() over(Order by salary desc),salary from employee
    Rank       Salary
    -----        -----------
      1            6678.00
      2            6456.00
      3            6123.00
      4            5890.00
      5            5567.00
      6            5567.00
      7            5345.00
      8            4789.00
      9            4234.00
   
   
   

3. STDEV() :

   It computes the standard deviation of all the values listed in the column. Actually, it’s the square root of the variance.

   Examples:

   SELECT STDEV(salary) std_dev_of_budgets FROM employee
   
   std_dev_of_budgets
    -------------------
    869.30016552268989
   
   
   STDEV(col) = SQRT(VAR(col))

4. VAR() :

   It computes the variance of all the values listed in a column. i.e,
   It returns measure of how far the extreme low range or high range value is from the middle.

   Examples:

   SELECT VAR(salary) AS Expr1 FROM employee

5. VARP():

   It is similar to VAR() but finds variance of entire data not only extreme low or high.

   Examples:

   SELECT VARP(salary) AS Expr1 – - P Stands for Population. FROM employee

6. ROW_NUMBER() OVER():

   Returns the sequential number for each row within a result set, starting from 1.

   Syntax:

   ROW_NUMBER ( ) OVER ([<partition_by_clause>] <order_by_clause>)

   Examples:

   1. SELECT row_number() over(ORDER BY name) FROM student where name='aaa'=> 1
      
      
   2. Find the second maximum using ROW_NUMBER() function.
      

      // Following doesn’t work if column have repetitive values.

      Select Salary
      From ( SELECT salary, ROW_NUMBER() OVER(ORDER BY salary DESC) as c
             FROM employee) as t
      where t.c=2

      // The following works with repetitive valued columns.

      Select m1
      From ( SELECT m1, ROW_NUMBER() OVER (ORDER BY s1.m1 DESC) as r
               from  (SELECT DISTINCT m1 from student) as s1 ) as t
      Where t.r =2
      
      

      Interpretation: Inline-View S1: Select DISTINCT m1 from Student Inline-View t   :  Select m1, Row_Number() OVER (ORDER BY s1.m1 DESC) from S1 Main Query     : Select m1 from t where t.r=2
      Inline-View S1	Inline-View t	Main Query.
      M1 20 25 29 30	R M1 1 30 2 29 3 25 4 20	20