MIN

Updated 2023-10-13 20:25:57.123000

Syntax

SELECT [wct].[MIN] (<@X, float,>
<@N, int,>)

Description

Use the aggregate function MIN to calculate the nth smallest distinct value in the expression @X.

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 Type

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.

{"columns":[{"field":"div"},{"field":"team"},{"field":"wins","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"div":"CENTRAL","team":"CHW","wins":"63"},{"div":"CENTRAL","team":"MIN","wins":"66"},{"div":"EAST","team":"TOR","wins":"74"},{"div":"EAST","team":"BAL","wins":"85"},{"div":"EAST","team":"NYY","wins":"85"},{"div":"WEST","team":"HOU","wins":"51"},{"div":"WEST","team":"SEA","wins":"71"}]}