MAX

Updated 2023-10-13 20:26:35.397000

Syntax

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

Description

Use the aggregate function MAX to calculate the nth largest 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 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.

{"columns":[{"field":"div"},{"field":"team"},{"field":"wins","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"div":"CENTRAL","team":"CLE","wins":"92"},{"div":"EAST","team":"TBD","wins":"92"}]}