Updated: 6 August 2010
Use LARGE to calculate the kth largest value in a dataset. Use this function to return a value based on its relative standing.
Syntax
SELECT [wctStatistics].[wct].[LARGE] (
<@Values_TableName, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@K, float,>)
Arguments
@Values_TableName
the name, as text, of the table or view that contains the values to be used in the LARGE calculation.
@ColumnName
the name, as text, of the column in the table or view specified by @Values_TableName that contains the values to be used in the LARGE calculation.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @Values_TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
@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 Types
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 ('#l1','num','',NULL, 1)
This produces the following result
----------------------
14
(1 row(s) affected)
To select the third largest value in the table,
select wct.LARGE('#l1','num','',NULL, 3)
This produces the following result
----------------------
13
(1 row(s) affected)