Logical functions - GREATEST

This function returns the maximum value from a list of one or more expressions.

-- syntax:

GREATEST ( expression1 [ , ...expressionN ] )

Arguments:

expression1, expressionN

A list of comma-separated expressions of any comparable data type. The GREATEST function requires at least one argument and supports no more than 254 arguments.

Each expression can be a constant, variable, column name or function, and any combination of arithmetic, bitwise, and string operators. Aggregate functions and scalar subqueries are permitted.

Return types:

Returns the data type with the highest precedence from the set of types passed to the function.

If all arguments have the same data type and the type is supported for comparison, GREATEST returns that type.

Otherwise, the function will implicitly convert all arguments to the data type of the highest precedence before comparison and use this type as the return type.

For numeric types, the scale of the return type will be the same as the highest precedence argument, or the largest scale if more than one argument is of the highest precedence data type.

-- Return maximum value from a list of constants

SELECT GREATEST('6.62', 3.1415, N'7') AS GreatestVal;

GO

-- Return maximum value from a list of character constants

SELECT GREATEST('Glacier', N'Joshua Tree', 'Mount Rainier') AS GreatestString;

GO

-- Return maximum value from a list of column arguments

SELECT P.Name,

    P.SellStartDate,

    P.DiscontinuedDate,

    PM.ModifiedDate AS ModelModifiedDate,

    GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) AS LatestDate

FROM SalesLT.Product AS P

INNER JOIN SalesLT.ProductModel AS PM

    ON P.ProductModelID = PM.ProductModelID

WHERE GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) >= '2003-01-01'

    AND P.SellStartDate >= '2003-01-01'

    AND P.Name LIKE 'Touring %'

ORDER BY P.Name;

-- Use GREATEST with local variables

CREATE TABLE dbo.Studies (

    VarX VARCHAR(10) NOT NULL,

    Correlation DECIMAL(4, 3) NULL

    );

INSERT INTO dbo.Studies

VALUES ('Var1', 0.2),

    ('Var2', 0.825),

    ('Var3', 0.61);

GO

DECLARE @PredictionA DECIMAL(2, 1) = 0.7;

DECLARE @PredictionB DECIMAL(3, 1) = 0.65;

SELECT VarX,

    Correlation

FROM dbo.Studies

WHERE Correlation > GREATEST(@PredictionA, @PredictionB);

GO

 

-- Use GREATEST with columns, constants, and variables

CREATE TABLE dbo.Studies (

    VarX VARCHAR(10) NOT NULL,

    Correlation DECIMAL(4, 3) NULL

    );

INSERT INTO dbo.Studies

VALUES ('Var1', 0.2),

    ('Var2', 0.825),

    ('Var3', 0.61);

GO

DECLARE @VarX DECIMAL(4, 3) = 0.59;

SELECT VarX,

    Correlation,

    GREATEST(Correlation, 0, @VarX) AS GreatestVar

FROM dbo.Studies;

GO

 

Source: Microsoft