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.
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() functionIf 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()functionIf 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() functionIf 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)
INSERT INTO #EXMAPLE_TABLE(SNAME, GRADE)
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],
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, GRADEFROM #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