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)
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?
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.
Access-SQL Guy,
Can you provide sample script DDL, Data and expected output?
thanks, didnt know you could use MAX() on set of static values that way.
Thanks Pinal
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;
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
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.
I need your help, how to display data with last date and last time with Max function or another function in sql server..
This needs understanding of your table schema and desired output.
saved me once again! Great work and explanation!
Thank you so much! I used Example 2 and it worked!