SMALL_q
Updated 2023-10-24 14:15:41.283000
Syntax
SELECT [westclintech].[wct].[SMALL_q] (
<@Values_RangeQuery, nvarchar(4000),>
,<@K, float,>)
Description
Use SMALL_q to calculate the kth smallest 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 SMALL_q calculation.
@K
is the position (from the smallest) 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 is less than or equal to zero or @K is greater than the number of rows in the dataset, SMALL_q returns an error.
If n is the number of rows in the dataset the @K = 1 returns the smallest value in the dataset and @K = n returns the largest value in the dataset.
No GROUP BY is required for this function even though it produces aggregated results.
Examples
`
CREATE TABLE #s1
(
[num] [float] NOT NULL
);
INSERT INTO #s1
VALUES
(1 );
INSERT INTO #s1
VALUES
(2 );
INSERT INTO #s1
VALUES
(2 );
INSERT INTO #s1
VALUES
(2 );
INSERT INTO #s1
VALUES
(2 );
INSERT INTO #s1
VALUES
(3 );
INSERT INTO #s1
VALUES
(4 );
INSERT INTO #s1
VALUES
(5 );
INSERT INTO #s1
VALUES
(6 );
INSERT INTO #s1
VALUES
(7 );
INSERT INTO #s1
VALUES
(8 );
INSERT INTO #s1
VALUES
(8 );
INSERT INTO #s1
VALUES
(8 );
INSERT INTO #s1
VALUES
(8 );
INSERT INTO #s1
VALUES
(9 );
INSERT INTO #s1
VALUES
(10 );
INSERT INTO #s1
VALUES
(11 );
INSERT INTO #s1
VALUES
(12 );
INSERT INTO #s1
VALUES
(13 );
INSERT INTO #s1
VALUES
(13 );
INSERT INTO #s1
VALUES
(14 );
To select the smallest number in the table:
select wct.SMALL_q('SELECT num from #s1', 1);
This produces the following result
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"1"}]}
To select the 5th smallest number in the table:
select wct.SMALL_q('SELECT num from #s1', 5);
This produces the following result
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"2"}]}