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

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?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,315 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,523 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 99,461 Reputation points 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.)

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points

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


    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.

    1 person found this answer helpful.
    0 comments No comments