Updated: 6 August 2010

Use PERCENTRANK_q 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.

SELECT [wctStatistics].[wct].[PERCENTRANK_q] (

<@Values_RangeQuery, nvarchar(4000),>

,<@X, float,>

,<@Significance, float,>)

the select statement, as text, used to determine the first set of values to be used in the PERCENTILE_q calculation.

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**.

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**.

float

· If *@Significance* is less than one or greater than 15, 3 significant digits are returned.

· If *@X* does not match one of the values in the dataset, PERCENTRANK_q interpolates to determine the correct percentage rank.

· No GROUP BY is required for this function even though it produces aggregated results.

· If there is only one value in the dataset and it is equal to @x, the value 1 will be returned by the function. If it does not match @x then NULL is returned.

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_q('Select num from #p1',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_q('Select num from #p1',10,5)

This produces the following result

----------------------

0.74074

(1 row(s) affected)