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,…)
LEAST(value1, value2,…)
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
SELECT LEAST(4.7890, '5.1', N'3');
Output:
-------
3.0000
- Strings
SELECT GREATEST('John Smith', 'Anna Black', N'Bob Low');
Output:
----------
John Smith
SELECT LEAST('John Smith', 'Anna Black', N'Bob Low');
Output:
----------
Anna Black
- 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
Top comments (2)
I posted similar article (dev.to/rozhnev/sql-server-2022-log...)
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.