Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

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
XLeratorDB MIN function syntax for SQL Server
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

 

See Also


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service