LARGE
Updated 2023-10-23 20:22:51.147000
Syntax
SELECT [westclintech].[wct].[LARGE] (
<@x, float,>
,<@k, int,>)
Description
Use the aggregate function LARGE to calculate the kth largest value in a dataset. Use this function to return a value based on its relative standing.
Arguments
@x
the input values for the function. @x is an expression of type float or of a type that can be implicitly converted to float.
@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.
@k must remain invariant for a group.
LARGE is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.
Examples
In this SQL we select the largest value.
SELECT wct.LARGE( x, --@Known_x
1 --@k
) as LARGE
FROM
(
VALUES
(1),
(2),
(2),
(2),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(8),
(8),
(8),
(9),
(10),
(11),
(12),
(13),
(13),
(14)
) n (x);
This produces the following result
{"columns":[{"field":"LARGE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"LARGE":"14"}]}
To select the third largest value in the table,
SELECT wct.LARGE( x, --@Known_x
3 --@k
) as LARGE
FROM
(
VALUES
(1),
(2),
(2),
(2),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(8),
(8),
(8),
(9),
(10),
(11),
(12),
(13),
(13),
(14)
) n (x);
This produces the following result
{"columns":[{"field":"LARGE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"LARGE":"13"}]}
Since LARGE is an aggregate function, we can take advanatge of the grouping capabilities of aggregates. In this example, we calculate the 5th largest value in each group.
SELECT grp,
wct.LARGE(x, 5) as LARGE
FROM
(
VALUES
('Group 1', 330854662),
('Group 1', -787948950),
('Group 1', 104760636),
('Group 1', -99072558),
('Group 1', 326055082),
('Group 1', 401353001),
('Group 1', 143405916),
('Group 1', 420320581),
('Group 1', -743843531),
('Group 1', 839451820),
('Group 2', -196956602),
('Group 2', -578645024),
('Group 2', 31690798),
('Group 2', 332317230),
('Group 2', 973774976),
('Group 2', -263698684),
('Group 2', 645094628),
('Group 2', 718352395),
('Group 2', -358941981),
('Group 2', 447414655),
('Group 3', -435711121),
('Group 3', -490640272),
('Group 3', -665860253),
('Group 3', 480661704),
('Group 3', -456298282),
('Group 3', 437436241),
('Group 3', -6535783),
('Group 3', 311292734),
('Group 3', -150346309),
('Group 3', 194606550),
('Group 4', 995355206),
('Group 4', -794694938),
('Group 4', -310339872),
('Group 4', 461478744),
('Group 4', 647091760),
('Group 4', -625559637),
('Group 4', -65067110),
('Group 4', 477468966),
('Group 4', -765348260),
('Group 4', 596374768),
('Group 5', 302764911),
('Group 5', 226227458),
('Group 5', 763226571),
('Group 5', -151647754),
('Group 5', 422758913),
('Group 5', -264094908),
('Group 5', 914420900),
('Group 5', 48940564),
('Group 5', -759334474),
('Group 5', 408007858)
) n (grp, x)
GROUP BY grp;
This produces the following result.
{"columns":[{"field":"grp","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"LARGE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"grp":"Group 1","LARGE":"326055082"},{"grp":"Group 2","LARGE":"332317230"},{"grp":"Group 3","LARGE":"-6535783"},{"grp":"Group 4","LARGE":"461478744"},{"grp":"Group 5","LARGE":"302764911"}]}
And, we could even do something like getting the second largest of the fifth largest from the groups.
SELECT wct.LARGE(g.y, 2) as LARGE
FROM
(
SELECT grp,
wct.LARGE(x, 5) as LARGE
FROM
(
VALUES
('Group 1', 330854662),
('Group 1', -787948950),
('Group 1', 104760636),
('Group 1', -99072558),
('Group 1', 326055082),
('Group 1', 401353001),
('Group 1', 143405916),
('Group 1', 420320581),
('Group 1', -743843531),
('Group 1', 839451820),
('Group 2', -196956602),
('Group 2', -578645024),
('Group 2', 31690798),
('Group 2', 332317230),
('Group 2', 973774976),
('Group 2', -263698684),
('Group 2', 645094628),
('Group 2', 718352395),
('Group 2', -358941981),
('Group 2', 447414655),
('Group 3', -435711121),
('Group 3', -490640272),
('Group 3', -665860253),
('Group 3', 480661704),
('Group 3', -456298282),
('Group 3', 437436241),
('Group 3', -6535783),
('Group 3', 311292734),
('Group 3', -150346309),
('Group 3', 194606550),
('Group 4', 995355206),
('Group 4', -794694938),
('Group 4', -310339872),
('Group 4', 461478744),
('Group 4', 647091760),
('Group 4', -625559637),
('Group 4', -65067110),
('Group 4', 477468966),
('Group 4', -765348260),
('Group 4', 596374768),
('Group 5', 302764911),
('Group 5', 226227458),
('Group 5', 763226571),
('Group 5', -151647754),
('Group 5', 422758913),
('Group 5', -264094908),
('Group 5', 914420900),
('Group 5', 48940564),
('Group 5', -759334474),
('Group 5', 408007858)
) n (grp, x)
GROUP BY grp
) g(x, y);
This produces the following result.
{"columns":[{"field":"LARGE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"LARGE":"332317230"}]}