SQL Server RANK.AVG function

Use the table-valued function RANK_AVG to calculate the ranks for a collection of x- and y-values. Tied ranks receive the average of ranks for the tied value. Ranks are calculated in ascending order.

Syntax

SELECT*FROM [wctStatistics].[wct].[RANK_AVG](

<@x_y_Query,nvarchar(max),>)

Arguments

@x_y_Query

a T-SQL statement, as a string, that specifies the x- and y-values. The x- and y-values must be of the type float or of a type that implicitly converts to float.

Return Type

RETURNSTABLE(

[x] [float] NULL,

[y] [float] NULL,

[xrank] [float] NULL,

[yrank] [float] NULL

)

Column

Column Description

x

the supplied x-value

y

the supplied y-value

xrank

the average rank of the x-value in the dataset of x-values

yrank

the average rank of the y-value in the dataset of y-values

Remarks

·The function is insensitive to order; it does not matter what order the x- and y-values are passed in.

·The x- and y-values pairs will be returned along with the average ranks.

Examples

SELECT *

INTO #r

FROM (

SELECT 125,110 UNIONALL

SELECT 115,122 UNIONALL

SELECT 130,125 UNIONALL

SELECT 140,120 UNIONALL

SELECT 140,140 UNIONALL

SELECT 115,124 UNIONALL

SELECT 140,123 UNIONALL

SELECT 125,137 UNIONALL

SELECT 140,135 UNIONALL

SELECT 135,145

) n(x,y)

SELECT *

FROM wct.RANK_AVG('SELECT x,y from #r')

DROPTABLE #r

This produces the following result.

We could also have passed the same data into the function with the following syntax.

SELECT *

FROM wct.RANK_AVG('SELECT 125,110 UNION ALL

SELECT 115,122 UNION ALL

SELECT 130,125 UNION ALL

SELECT 140,120 UNION ALL

SELECT 140,140 UNION ALL

SELECT 115,124 UNION ALL

SELECT 140,123 UNION ALL

SELECT 125,137 UNION ALL

SELECT 140,135 UNION ALL

SELECT 135,145 ')

This produces the following result.

Since this is a table –valued function, you can re-arrange the output using TSQL. In the following example, we will put the data in x-value order and we will move the xrank column next to the x column.