SQL Server RANK_AVG Function

Updated 2023-06-15 22:14:55.007000

Description

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 [westclintech].[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

table

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 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
) n(x,y);
 
SELECT *
FROM wct.RANK_AVG('SELECT x,y from #r')
DROP TABLE #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.

SELECT x,xrank,y,yrank
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 ')
ORDER BY 1, 3;

This produces the following result.