Why MS SQL does not have MAX function between multiple values?

Question

Hi all,

I'm working on MS SQL and readlly need Max function between multible value ( like: greatest function in MySQL, example: Max(value1, value2, value3...), I have to use alternative solution: "select max(column) FROM (VALUES (1), (2), (3),...) )'
so I have a question: Why MS SQL does not have MAX function between multiple values?

Thanks,
Cuong

Answer 1

Hey, I have some news for you! This query runs on Azure SQL Database:

SELECT greatest(1, 2, 4, 5), least('Hugo', 'Laura', 'Adele', 'Oscar')

It is not available in SQL 2019, but since Microsoft works with "cloud first", I would be very surprised if these functions will not appear in SQL 2022, which Microsoft recently announced. (Currently only in private preview.)

Answer 2

Hi @Cuong, Nguyen Duy [AP] ,

Welcome to the microsoft TSQL Q&A forum!

Personally, I think this design is because SQL Server is a relational database, and its processing of data is generally based on tables, and the processing of data in tables is generally based on certain columns. So if you want to get the maximum value of multiple values, the designer may think that you can store them in a column and then use the MAX function.

Of course, the functions of each version of SQL Server are constantly improving.As Erland said, perhaps the new functions he mentioned will appear in the latest version. If you have any suggestions for SQL Server, you can submit them in the link below:
Share your Ideas

If you need to get the maximum value of multiple variables,you can use the IF or CASE statement to do this, but this will make you write a very long chode, and you must implement the logic manually. You can currently consider the following methods:

Method 1: With Variables

DECLARE @Int1 INT = 1, @Int2 INT = 3, @Int3 INT = 5;  
SELECT MAX(v)  
FROM (VALUES (@Int1), (@Int2), (@Int3)) AS value(v);  

Method 2: With Static Values

SELECT MAX(v)  
FROM (VALUES (1),(5),(3)) AS value(v);  

Method 3: With Columns

CREATE TABLE SampleTable  
( ID INT PRIMARY KEY,  
Int1 INT,  
Int2 INT,  
Int3 INT);  
INSERT INTO SampleTable (ID, Int1, Int2, Int3)  
VALUES (1, 1, 2, 3);  
INSERT INTO SampleTable (ID, Int1, Int2, Int3)  
VALUES (2, 3, 2, 1);  
INSERT INTO SampleTable (ID, Int1, Int2, Int3)  
VALUES (3, 1, 3, 2);  
-- Query to select maximum value  
SELECT ID,  
(SELECT MAX(v)  
FROM (VALUES (Int1), (Int2), (Int3)) AS value(v)) AS MaxValue  
FROM SampleTable;  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.