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

@N

the rank to be returned. @N must be of the type int or of a type that implicitly converts to int.

@X

the values to be evaluated. @X must be of the type float or of a type that implicitly converts to float.

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"}]}