Updated: 6 August 2010
Use PERCENTRANK to return the rank of a value in a dataset as a percentage of the dataset. This function can be used to evaluate the relative standing of a value within a dataset.
Syntax
SELECT [wctStatistics].[wct].[PERCENTRANK] (
<@Values_TableName, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@X, float,>
,<@Significance, float,>)
Arguments
@Values_TableName
the name, as text, of the table or view that contains the values to be used in the PERCENTRANK 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 PERCENTRANK 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.
@X
is the value for which you want to know the rank. @X is an expression of type float or of a type that can be implicitly converted to float.
@Significance
is a value that identifies the number of significant digits for the returned percentage value. If @Significance is NULL, PERCENTRANK uses three digits (0.xxx). @Significance is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· If @Significance is less than one, PERCENTRANK returns an error.
· If @X does not match one of the values in the dataset, PERCENTRANK interpolates to determine the correct percentage rank.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #p1(
[num] [float] NOT NULL
)
INSERT INTO #p1 VALUES (1)
INSERT INTO #p1 VALUES (1)
INSERT INTO #p1 VALUES (1)
INSERT INTO #p1 VALUES (2)
INSERT INTO #p1 VALUES (3)
INSERT INTO #p1 VALUES (4)
INSERT INTO #p1 VALUES (8)
INSERT INTO #p1 VALUES (11)
INSERT INTO #p1 VALUES (12)
INSERT INTO #p1 VALUES (13)
To find the PERCENTRANK of 1
select wct.PERCENTRANK('#p1','num','',NULL,1,NULL)
This produces the following result
----------------------
0
(1 row(s) affected)
To find the PERCENTRANK of 10 to 5 decimal places
select wct.PERCENTRANK('#p1','num','',NULL,10,5)
This produces the following result
----------------------
0.74074
(1 row(s) affected)