DEV Community

Olesia Dudareva
Olesia Dudareva

Posted on • Updated on

Don't miss out on the new features in SQL Server 16.x

The latest version of SQL Server has some exciting updates as well as some very useful new features. Today I would like to look at GREATEST and LEAST, two opposite functions for getting the maximum and minimum values in a list.

GREATEST & LEAST

They respectively return the maximum and minimum values from the list of values. There are some restrictions: the list must contain at least one value; the maximum number of values in the list is 254.
Both functions do not support some data types, for example, cursor, geometry, geography, image, table, ntext, text, xml.

Syntax

GREATEST(value1, value2,)
Enter fullscreen mode Exit fullscreen mode
LEAST(value1, value2,)
Enter fullscreen mode Exit fullscreen mode

Input parameters can be, for instance, variables, column names, functions, and any combination of arithmetic, and string operators.

Return data type

The functions return the data type with the highest precedence. If all values in the list are of the same type, it will be returned by the functions. If not, they will try to convert them. As a result, it is important that all values in the list be convertible.

Examples

  • Constant numbers: the scale = 4 because it is determined by the scale of 4.7890 (the highest precedence data type).
SELECT GREATEST(4.7890, '5.1', N'3');

Output:
-------
 5.1000
Enter fullscreen mode Exit fullscreen mode
SELECT LEAST(4.7890, '5.1', N'3');

Output:
-------
 3.0000
Enter fullscreen mode Exit fullscreen mode
  • Strings
SELECT GREATEST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
John Smith
Enter fullscreen mode Exit fullscreen mode
SELECT LEAST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
Anna Black
Enter fullscreen mode Exit fullscreen mode
  • Combination of variables, table column and constant
CREATE TABLE Currency (
    Currency_Name VARCHAR(3),
    Currency_Rate DECIMAL(10,4)
    );

INSERT INTO Currency
VALUES ('USD', 0.82),
    ('USD', 0.8101),
    ('USD', 0.8058);
GO

DECLARE @Default_Rate DECIMAL(10, 4) = 0.8195;

SELECT Currency_Name,
    Currency_Rate,
    GREATEST(Currency_Rate, 0.8000, @Default_Rate) AS GreatestRate,
    LEAST(Currency_Rate, 0.8000, @Default_Rate) AS LeastRate
FROM Currency;
GO

Output:
Currency_Name Currency_Rate GreatestRate LeastRate   
------------- ------------- ------------ ------------
USD                   .8200        .8200        .8000
USD                   .8101        .8195        .8000
USD                   .8058        .8195        .8000
Enter fullscreen mode Exit fullscreen mode

Top comments (2)

Collapse
 
rozhnev profile image
Slava Rozhnev

I posted similar article (dev.to/rozhnev/sql-server-2022-log...)

Collapse
 
notte profile image
Olesia Dudareva

Great! I just added a little more information about these amazing functions. They can definitely be very useful, but as usual, there are some limitations that developers should be aware of.