Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server function to calculate the nth largest distinct value


MAX

Updated: 31 March 2014


Use the aggregate function MAX to calculate the nth largest distinct value in the expression @X.
Syntax
XLeratorDB syntax for MAX function 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 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.
·         Available in XLeratorDB / math 2008 only
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.
div     team        wins
------- ---- -----------
CENTRAL CLE           92
EAST    TBD           92

 

See Also


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service