MAX
Updated: 31 March 2014
Use the aggregate function MAX to calculate the nth largest distinct value in the expression @X.
Syntax
Arguments
@X
the values to be evaluated. @X must be of the type float or of a type that implicitly converts to float.
@N
the rank to be returned. @N must be of the type int or of a type that implicitly converts to int.
Return Types
float
Remarks
· To return the largest value in @X, set @N equal to 1. To return the 2nd largest distinct value, set @N = 2. For the third, @N = 3, etc.
· If @N is NULL then @N = 1.
· If @N is less than 1 than NULL is returned.
· If @N is larger than the number of distinct values in @X, then NULL is returned.
· To return the nth largest non-distinct value, us the LARGE function.
· Since MAX is a SQL CLR aggregate, it may not be followed by an OVER clause.
Examples
In this example, we want to SELECT the top 2 second place teams in each division.
WITH mycte as (
SELECT
*
FROM (VALUES
('BAL','EAST',85),
('BOS','EAST',97),
('CHW','CENTRAL',63),
('CLE','CENTRAL',92),
('DET','CENTRAL',93),
('HOU','WEST',51),
('KCR','CENTRAL',86),
('ANA','WEST',78),
('MIN','CENTRAL',66),
('NYY','EAST',85),
('OAK','WEST',96),
('SEA','WEST',71),
('TBD','EAST',92),
('TEX','WEST',91),
('TOR','EAST',74)
)n(team,div,wins)
)
SELECT TOP 2
m1.div,
m2.team,
m2.wins
FROM
mycte m1
JOIN
mycte m2
ON
m1.div = m2.div
GROUP BY
m1.div,
m2.team,
m2.wins
HAVING
wct.MAX(m1.wins,2) = m2.wins
ORDER BY
m2.wins DESC
This produces the following result.
div team wins
------- ---- -----------
CENTRAL CLE 92
EAST TBD 92
See Also