Ranking Function

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic and return Big Integer values.  

This article is related to RANKING Function.

ROW_NUMBER function

The ROW_NUMBER function returns the row number over a named or unnamed window specification.

The ROW_NUMBER function does not take any arguments, and for each row over the window it returns an ever increasing BIGINT. It is normally used to limit the number of rows returned for a query. The LIMIT keyword used in other databases is not defined in the SQL standard, and is not supported.

• Derby does not currently allow the named or unnamed window specification to be specified in the OVER() clause, but requires an empty parenthesis. This means the function is evaluated over the entire result set.
• The ROW_NUMBER function cannot currently be used in a WHERE clause.
• Derby does not currently support ORDER BY in sub queries, so there is currently no way to guarantee the order of rows in the SELECT sub query. An optimizer override can be used to force the optimizer to use an index ordered on the desired column(s) if ordering is a firm requirement.

RANK()  function

If two or more rows tie for a rank, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

The sort order that is used for the whole query determines the order in which the rows appear in a result set.

DENSE_RANK()function

If two or more rows tie for a rank in the same partition, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.

NTILE()  function

If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.
Example are mentioned bellow

CREATE TABLE #EXMAPLE_TABLE

       ( CODE    INT IDENTITY(1,1) NOT NULL,

         SNAME   VARCHAR(50)       NOT NULL,

         GRADE   CHAR(1)           NOT NULL)

GO

INSERT INTO #EXMAPLE_TABLE(SNAME, GRADE)

VALUES

('Raja', 'A'),   

('Prja', 'B'),

('Saja', 'C'),

('Khaja', 'A'),

('Baja', 'B'),

('Balaram', 'C'),

('Majhi', 'B'),

('Sajal', 'B'),

('Sarita', 'C'),

('Sarika', 'C')  

SELECT    ROW_NUMBER() OVER(ORDER BY GRADE) [ROW_NUMBER],

              RANK() OVER(ORDER BY GRADE) [RANK],

              DENSE_RANK() OVER(ORDER BY GRADE) [DENSE_RANK],

              NTILE(4) OVER(ORDER BY GRADE) [NTILE],

          SNAME, GRADE

FROM      #EXMAPLE_TABLE  

ROW_NUMBER  RANK  DENSE_RANK  NTILE       SNAME GRADE

1           1     1     1     Raja        A

2           1     1     1     Khaja       A

3           3     2     1     Baja        B

4           3     2     2     Prja        B

5           3     2     2     Majhi       B

6           3     2     2     Sajal       B

7           7     3     3     Sarita      C

8           7     3     3     Sarika      C

9           7     3     4     Saja        C

10          7     3     4     Balaram     C

SELECT    ROW_NUMBER() OVER(PARTITION BY GRADE ORDER BY GRADE) [ROW_NUMBER],

          SNAME, GRADE

FROM      #EXMAPLE_TABLE

ROW_NUMBER        SNAME       GRADE

1                 Raja        A

2                 Khaja       A

1                 Baja        B

2                 Prja        B

3                 Majhi       B

4                 Sajal       B

1                 Sarita      C

2                 Sarika      C

3                 Saja        C

4                 Balaram     C

Hope you like it.

Posted by: MR. JOYDEEP DAS