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

asked 2021-11-22T10:26:32.957+00:00
Cuong, Nguyen Duy [AP] 21 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,476 questions
{count} votes

Accepted answer
  1. answered 2021-11-22T23:01:36.983+00:00
    Erland Sommarskog 67,721 Reputation points Microsoft MVP

    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.)

    No comments

1 additional answer

Sort by: Most helpful
  1. answered 2021-11-23T02:06:53.737+00:00
    EchoLiu-MSFT 14,416 Reputation points

    Hi @Cuong, Nguyen Duy [AP] ,

    Welcome to the microsoft TSQL Q&A forum!

    151597-image.png
    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.

    No comments