SQL GREATEST and LEAST Functions Alternative in Synapse and TSQL

Many relational databases such as Oracle, Teradata supports GREATEST and LEAST functions. These functions are quite useful when you are comparing data from two or more columns. But, Azure Synapse data warehouse which is based on the Microsoft SQL Server TSQL does not support GREATEST and LEAST functions. You either have to use a case statement or write your own user defined functions (UDFs). In this article, we will check what are the GREATEST and LEAST Functions Alternative in Synapse and TSQL.

GREATEST and LEAST Functions Alternative in Synapse and TSQL

The GREATEST and LEAST functions are used to return largest and smallest values from the list of columns or expressions respectively.

GREATEST Function Alternative in Synapse and TSQL

The GREATEST function returns the “greatest” or largest value in a set of values that you provide to it.

For example, consider following Teradata example.

Select GREATEST(13,5);
GREATEST(13,5)
--------------
            13

Note that, the greatest function works on integer, character and date type.

However, same function does not work on Synapse and MSSQL TSQL.

Following list is an alternate approach that you can use. Note that, for simplicity, we have used only two expressions.

CASE Statement as a GREATEST Function Alternative
GREATEST User Defined Function

Now, let us check these two approach in brief.

CASE Statement as a GREATEST Function Alternative in TSQL

The CASE statement in TSQL evaluates a list of conditions and returns one of multiple possible result expressions.

We can use the CASE statement to check largest of two columns or expression. For a simplicity, we have used two expressions.

For examples,

SELECT CASE WHEN 13 < 6 THEN 6 ELSE 13 END as GREATEST;
GREATEST
-----------
         13

SELECT CASE WHEN 'apples' < 'alpha' THEN 'alpha' ELSE 'apples' END as GREATEST;
GREATEST
--------
apples

SELECT CASE WHEN cast('2003-08-17 12:15:24.756' as datetime) < cast('2003-08-17 12:15:24.456' as datetime) THEN cast('2003-08-17 12:15:24.456' as datetime)
ELSE cast('2003-08-17 12:15:24.756' as datetime) END as GREATEST;
GREATEST
-----------------------
2003-08-17 12:15:24.757

GREATEST User Defined Function in TSQL

Alternatively, you can create GREATEST user defined function in an Azure Synapse data warehouse or MSSQL TSQL.

Following is the greatest user defined function to compare two columns or expressions.

CREATE FUNCTION [dbo].[greatest] (@val1 [varchar](max),@val2 [varchar](max)) 
RETURNS varchar(max)
AS
begin
if (isnumeric(@val1)=1  and isnumeric(@val2)=1)
    begin
    if cast(@val1 as bigint)> cast(@val2 as bigint)
       return @val1
    end
else if (isdate(@val1)=1 and isdate(@val2)=1)
    begin
    if @val1>@val2
       return @val1
    end
else
    begin
    if @val1>@val2
       return @val1
    end
    return isnull(@val2,@val1)
end
GO

And following is the output.

select dbo.greatest(13, 6);
GREATEST
-----------
         13

select dbo.greatest('apples','alpha');
GREATEST
--------
apples

select dbo.greatest('2003-08-17 12:15:24.756', '2003-08-17 12:15:24.456');
GREATEST
-----------------------
2003-08-17 12:15:24.757

LEAST Function Alternative in Synapse and TSQL

The LEAST function returns the “least” or smallest value in a set of values that you provide to it.

For example, consider following Teradata example.

select least(13,5);
least(13,5)
-----------
          5

Just like the greatest, least function works on integer, character and date type.

As LEAST function is also not supported in TSQL. Following list is an alternate approach that you can use. Note that, for simplicity, we have used only two expressions.

CASE Statement as a LEAST Function Alternative
LEAST User Defined Function

Now, let us check these two approach in brief.

CASE Statement as a LEAST Function Alternative in TSQL

We can use the CASE statement to check smallest of two columns or expression. For a simplicity, we have used two expressions.

For examples,

SELECT CASE WHEN 13 < 6 THEN 13 ELSE 6 END as LEAST;
LEAST
-----------
          6

SELECT CASE WHEN 'apples' < 'alpha' THEN 'apples' ELSE 'alpha' END as LEAST;
LEAST
-----
alpha


SELECT CASE WHEN cast('2003-08-17 12:15:24.756' as datetime) < cast('2003-08-17 12:15:24.456' as datetime) THEN cast('2003-08-17 12:15:24.756' as datetime) ELSE cast('2003-08-17 12:15:24.456' as datetime) END as LEAST;
LEAST
-----------------------
2003-08-17 12:15:24.457

LEAST User Defined Function in TSQL

Alternatively, you can create LEAST user defined function in an Azure Synapse data warehouse or MSSQL TSQL.

Following is the least user defined function to compare two columns or expressions.

CREATE FUNCTION [dbo].[least] (@val1 [varchar](max),@val2 [varchar](max)) 
RETURNS varchar(max)
AS
begin
if (isnumeric(@val1)=1  and isnumeric(@val2)=1) 
    begin
    if cast(@val1 as bigint)< cast(@val2 as bigint) 
       return @val1
    end
else if (isdate(@val1)=1 and isdate(@val2)=1)
    begin
    if @val1<@val2
       return @val1
    end
else 
    begin
    if @val1<@val2
       return @val1
    end 
    return isnull(@val2,@val1)
end
GO

And following is the output.

select dbo.least(13, 6);
LEAST
-----------
          6

select dbo.least('apples','alpha');
LEAST
-----
alpha

select dbo.least('2003-08-17 12:15:24.756', '2003-08-17 12:15:24.456');
LEAST
-----------------------
2003-08-17 12:15:24.457