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);
This produces the following result
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"19"}]}