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

Microsoft SQL Server Training Online Learning Classes Built in functions Ranking


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

1 comment: