Updated: 01 February 2009
Use wct.RANK to return the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list.
Syntax
SELECT [wctStatistics].[wct].[RANK] (
<@Number, float,>
,<@Values_TableName, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@Order, float,>)
Arguments
@Number
is the number whose rank you want to find. @Number is an expression of type float or of a type that can be implicitly converted to float.
@TableName
the name, as text, of the table or view that contains the values to be used in the RANK calculation.
@ColumnName
the name, as text, of the column in the table or view specified by @Values_TableName that contains the values to be used in the RANK calculation.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @Values_TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
@Order
is a number specifying how to rank @Number. If @Order is zero or NULL, then RANK is calculated in descending order, Otherwise RANK is calculated in ascending order. @Order is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
· If @Number is not contained in the dataset, RANK returns an error.
· For more complex queries, use the RANK_q function.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #r1(
[num] [float] NOT NULL
)
INSERT INTO #r1 VALUES (1)
INSERT INTO #r1 VALUES (2)
INSERT INTO #r1 VALUES (2)
INSERT INTO #r1 VALUES (2)
INSERT INTO #r1 VALUES (2)
INSERT INTO #r1 VALUES (3)
INSERT INTO #r1 VALUES (4)
INSERT INTO #r1 VALUES (5)
INSERT INTO #r1 VALUES (6)
INSERT INTO #r1 VALUES (7)
INSERT INTO #r1 VALUES (8)
INSERT INTO #r1 VALUES (8)
INSERT INTO #r1 VALUES (8)
INSERT INTO #r1 VALUES (8)
INSERT INTO #r1 VALUES (9)
INSERT INTO #r1 VALUES (10)
INSERT INTO #r1 VALUES (11)
INSERT INTO #r1 VALUES (12)
INSERT INTO #r1 VALUES (13)
INSERT INTO #r1 VALUES (13)
INSERT INTO #r1 VALUES (14)
To ascertain where the number 2 ranks, from highest to lowest
select wct.RANK(2, '#r1','num','', NULL,NULL)
This produces the following result
----------------------
17
(1 row(s) affected)
To ascertain where the number 13 ranks, from lowest to highest
select wct.RANK(13, '#r1','num','', NULL,2)
This produces the following result
----------------------
19
(1 row(s) affected)