MIN
Updated: 31 March 2014
Use the aggregate function MIN to calculate the nth smallest 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 smallest value in @X, set @N equal to 1. To return the 2nd smallest 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 smallest non-distinct value, us the SMALL function.
· Since MIN is a SQL CLR aggregate, it may not be followed by an OVER clause.
Examples
In this example, we want to SELECT the bottom 2 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
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.MIN(m1.wins,2) >= m2.wins
ORDER BY
div,
wins
This produces the following result.
div team wins
------- ---- -----------
CENTRAL CHW 63
CENTRAL MIN 66
EAST TOR 74
EAST BAL 85
EAST NYY 85
WEST HOU 51
WEST SEA 71
See Also