LARGE_q
Updated 2023-10-23 20:28:25.513000
Syntax
SELECT [westclintech].[wct].[LARGE_q] (
<@Values_RangeQuery, nvarchar(4000),>
,<@K, float,>)
Description
Use LARGE_q to calculate the kth largest value in a dataset. Use this function to return a value based on its relative standing.
Arguments
@Values_RangeQuery
the select statement, as text, used to determine the values to be used in the LARGE_q calculation.
@K
is the position (from the largest) in the dataset to return. @K is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If @K = 0 or @K is greater than the number of rows in the dataset, LARGE returns an error.
If n is the number of rows in the dataset then @K = 1 returns the largest value in the dataset and @K = n returns the smallest value in the dataset.
No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #l1
(
[num] [float] NOT NULL
);
INSERT INTO #l1
VALUES
(1 );
INSERT INTO #l1
VALUES
(2 );
INSERT INTO #l1
VALUES
(2 );
INSERT INTO #l1
VALUES
(2 );
INSERT INTO #l1
VALUES
(2 );
INSERT INTO #l1
VALUES
(3 );
INSERT INTO #l1
VALUES
(4 );
INSERT INTO #l1
VALUES
(5 );
INSERT INTO #l1
VALUES
(6 );
INSERT INTO #l1
VALUES
(7 );
INSERT INTO #l1
VALUES
(8 );
INSERT INTO #l1
VALUES
(8 );
INSERT INTO #l1
VALUES
(8 );
INSERT INTO #l1
VALUES
(8 );
INSERT INTO #l1
VALUES
(9 );
INSERT INTO #l1
VALUES
(10 );
INSERT INTO #l1
VALUES
(11 );
INSERT INTO #l1
VALUES
(12 );
INSERT INTO #l1
VALUES
(13 );
INSERT INTO #l1
VALUES
(13 );
INSERT INTO #l1
VALUES
(14 );
To select the largest value in the table,
select wct.LARGE_q('SELECT num from #l1', 1);
This produces the following result
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"14"}]}
To select the third largest value in the table,
select wct.LARGE_q('SELECT num from #l1', 3);
This produces the following result
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"13"}]}