2.2 Dense Rank
- If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank. For example, if the two top salespeople have the same SalesYTD value, they will both have a rank value of one. The salesperson with the next highest SalesYTD will have a rank value of two. This exceeds the number of distinct rows that come before the row in question by one. Therefore, the numbers returned by the DENSE_RANK function do not have gaps, and always have consecutive rank values.
- Returns rank for rows within the partition of result set without any gaps in the ranking.
QUERY: - DENSE_RANK ( ) OVER (< order_by_clause>) from Tbname;
EXAMPLE:-SELECT *, dense_rank() OVER ( order by grade desc) AS Dense_rank
FROM customer
WITH PARTITION BY CLAUSE:-
QUERY: - DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause>) From TableName
EXAMPLE: - SELECT *, dense_rank() OVER (partition by city order by grade desc)
AS Dense_rank FROM customer
Last modified: Tuesday, 17 December 2019, 12:35 PM