SQL Server 2022: Logical Functions - GREATEST & LEAST

#sql #tsql #tutorial

Now after SQL Server 2022 released we can test new features of T-SQL
One of the long-awaited innovations is support for the GREATEST and LEAST functions.

This functions returns the maximum (minimum) value from a list of one or more expressions as below.

SELECT GREATEST(1, 4, 7, 3, 9);

SELECT LEAST('z', 'a', 'f', 'z', '1');

As we can see the functions works with any comparable types. Using different types in same function can cause to error:

SELECT LEAST('z', 'a', 'f', 'z', '1', -1);

Both functions can be applied to several column within same row:

CREATE TABLE Tbl (a int, b int);

INSERT INTO Tbl VALUES (1, 3), (9, 2), (0, 0);

SELECT
    a,
    b,
    LEAST(a, b) least_ab,
    GREATEST(a, b) greatest_ab
FROM Tbl;

Test this and other features SQL Server 2022 on SQLize.online