SQL Server function to calculate the nth smallest distinct value

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.
·         Available in XLeratorDB / math 2008 only
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