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);