RANK_q

Updated 2023-10-24 13:46:15.760000

Syntax

SELECT [westclintech].[wct].[RANK_q] (
   <@Number, float,>
  ,<@Values_RangeQuery, nvarchar(4000),>
  ,<@Order, float,>)

Description

Use the scalar function RANK_q to return the rank of a number in a list of numbers.

Arguments

@Values_RangeQuery

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

@Order

is a number specifying how to rank @Number. If @Order is zero or NULL, then RANK_q is calculated in descending order, Otherwise RANK_q is calculated in ascending order. @Order is an expression of type float or of a type that can be implicitly converted to float.

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

Return Type

float

Remarks

RANK_q 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_q returns an error.

For simpler queries, consider using RANK.

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_q(2, 'Select num from #r1', NULL);

This produces the following result

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"17"}]}

To ascertain where the number 13 ranks, from lowest to highest

select wct.RANK_q(13, 'Select num from #r1', 2);