Updated: 01 February 2009

Use wct.LARGE to calculate the k^{th} largest value in a dataset. Use this function to return a value based on its relative standing.

SELECT [wctStatistics].[wct].[LARGE_q] (

<@Values_RangeQuery, nvarchar(4000),>

,<@K, float,>)

the select statement, as text, used to determine the values to be used in the LARGE_q calculation.

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

float

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

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

----------------------

14

(1 row(s) affected)

To select the third largest value in the table,

select wct.LARGE_q('SELECT num from #l1', 3)

This produces the following result

----------------------

13

(1 row(s) affected)