SQL SERVER – Finding Max Value From Multiple Values

Here is a question which I have received a few days ago.

“I have three different variables, I want to find out which one of them has the maximum or highest value. How can I do that?

I know I can do this with the IF or CASE statement, but that makes me write a long chode and I have to manually implement logic.

Is there any other way? “

Absolutely, here is the simple example which will work with SQL Server 2008 and later versions.

Example 1: With Variables

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

Example 2: With Static Values

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

Example 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;

I hope this simple queries helps you to find maximum value from various variables.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Do You Know Your Data’s Classification? – Notes from the Field #050
Next Post
EXCEL / SQL SERVER – Extract the Domain from an Email Address

Related Posts

No results found.

11 Comments. Leave new

  • hi, I need the output like below,

    DECLARE @t TABLE(a INT,b INT,c INT, a1 varchar(2), b1 varchar(2),c1 varchar(2));
    INSERT @t VALUES(1,2,3,’PT’,’UP’,’DL’),(9,8,7,’PT’,’UP’,’DL’),(4,6,5,’PT’,’UP’,’DL’);
    SELECT *
    , ( SELECT MAX(val)
    FROM (VALUES (a)
    , (b)
    , (c)
    ) AS value(val)
    ) AS MaxVal

    FROM @t;

    a b c a1 b1 c1 MaxVal CharVal
    1 2 3 PT UP DL 3 DL
    9 8 7 PT UP DL 9 PT
    4 6 5 PT UP DL 6 UP

    CharVal based on the MaxVal.
    If c is bigger then c1
    if a is bigger then a1.

    Any idea how to perform this?

    Reply
  • Access-SQL Guy
    January 13, 2015 7:21 pm

    Thanks, looked at dozens of sites. Yours was the best for this function!
    Now, to follow up I need a CASE statement that identifies what Column the max came from. It is not always just the max value itself. It often is knowing which column (or variable value in your first example) it came from.

    Reply
  • thanks, didnt know you could use MAX() on set of static values that way.
    Thanks Pinal

    Reply
  • SQL Server 2005 solution (values is not available):
    DECLARE @Int1 INT,@Int2 int, @Int3 int;
    set @Int1 = 1;
    set @Int2 = 3;
    set @Int3 = 5;
    SELECT MAX (v) from (SELECT @int1 as v UNION select @int2 as v UNION select @Int3 as v) a;

    Reply
  • i want to retrieve max id from table named master and after retrieving it i want to insert its value into java string and increment java string.
    Please suggest me any query to do it

    Reply
  • Excellent article, exactly what I was looking for.
    But following on from getting the max value, I have the same issue as Access-SQL Guy:
    My value columns comes from different underlying tables (in the join statements).
    So based on the max value that I get back, I need to link back to the underlying table to get additional info from that
    table where my id and value match.

    Perhaps more info.
    I track which one of the underlying tables have the latest audit info in, and then need to extract who created the relevant audit record based on the recordid and audit date.

    I hope you can help.

    Reply
  • Ricki Hidayat
    April 27, 2018 3:24 pm

    I need your help, how to display data with last date and last time with Max function or another function in sql server..

    Reply
  • saved me once again! Great work and explanation!

    Reply
  • Barbara Ulitsky
    May 7, 2020 3:48 am

    Thank you so much! I used Example 2 and it worked!

    Reply

Leave a Reply

Menu