SQL Server 2005 has total of 4 ranking function. Ranking functions
return a ranking value for each row in a partition. All the ranking
functions are non-deterministic.
ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set with gap in rank.
Returns the rank of each row within the partition of a result set with gap in rank.
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.
Distributes the rows in an ordered partition into a specified number of groups.
SELECT
ROW_NUMBER() OVER(ORDER BY sal DESC) AS 'row number',
RANK() OVER(ORDER BY sal DESC) AS 'rank',
DENSE_RANK() OVER(ORDER BY sal DESC)AS 'dense rank',
NTILE(3)OVER(ORDER BY sal DESC)AS 'Quartile'
,sal
from tbl_groupingSet
ROW_NUMBER() OVER(ORDER BY sal DESC) AS 'row number',
RANK() OVER(ORDER BY sal DESC) AS 'rank',
DENSE_RANK() OVER(ORDER BY sal DESC)AS 'dense rank',
NTILE(3)OVER(ORDER BY sal DESC)AS 'Quartile'
,sal
from tbl_groupingSet
Result will display as
No comments:
Post a Comment