Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

You'd need to make a User-Defined Function if you wanted to have syntax similar to your example, but could you do what you want to do, inline, fairly easily with a CASE statement, as the others have said.

You'd need to make a User-Defined Function if you wanted to have syntax similar to your example, but could you do what you want to do, inline, fairly easily with a CASE statement, as the others have said. The UDF could be something like this: create function dbo. @val2 int) returns int as begin if @val1 > @val2 return @val1 return isnull(@val2,@val1) end ....and you would call it like so: SELECT o.

OrderId, dbo. NegotiatedPrice, o. SuggestedPrice) FROM Order o.

7 I would support you solution, the only thing I would add is the support for NULL values. If you simply modify the final line: "return @value2" to read as: "return isnull(@val2,@val1)" then if one of the values is null the function will return the not null value, otherwise it will work as normal – kristof Sep 24 '08 at 10:32 1 What about other data types e.g. Would I need to write a gherIntegerArgument and a gherDateTimeArgument and a gherVarcharArgument and a ...? – onedaywhen Jun 10 '09 at 13:27 1 this will be incredibly slow, as all things scalar UDFs. Use inline UDFs instead – AlexKuznetsov Oct 21 '10 at 16:55 Can something similar be done to get the lowest value of the two?

– Thomas Dec 23 '10 at 8:24 1 @Thomas Replace > with.

Can be done in one line: SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2)) Edit: If you're dealing with very large numbers you'll have to convert the value variables into bigint in order to avoid an integer overflow.

2 +1 I believe you have provided the most correct way. "SELECT ((@val1+@val2) + ABS(@val1-@val2))/2 as MAX_OF_TWO" Also remember, "SELECT ((@val1+@val2) - ABS(@val1-@val2))/2 as MIN_OF_TWO". – tom Jun 4 '09 at 20:00 3 This way will give an overflow error if the sum is greater than can be stored in an int: declare @val1 int declare @val2 int set @val1 = 1500000000 set @val2 = 1500000000 SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2)) -- => overflow error – AakashM Jun 10 '09 at 13:02 1 I've never seen that before.Genius.

– locster Oct 26 '09 at 13:18 4 This is extremely "dirty" "trick". When programming your code should explicitly express the aim, however in your case it looks like code taken from obfuscation contest. – macias Jan 11 '11 at 9:50 1 It may be "dirty", but it could be the only option for databases with simple SQL dialects.

– splattne Jan 11 '11 at 12:16.

I don't think so. I wanted this the other day. The closest I got was: SELECT o.

OrderId, CASE WHEN o. NegotiatedPrice > o. SuggestedPrice THEN o.

NegotiatedPrice ELSE o. SuggestedPrice END FROM Order o.

This is my favorite method. You don't risk an overflow, and it's less cryptic than splattne's solution (which is cool btw), and I don't have the hassle of creating a UDF. Case is very handy in many situations.

– Lance Fisher Dec 23 '11 at 22:29.

DECLARE @MAX INT @MAX = (SELECT MAX(VALUE) FROM (SELECT 1 AS VALUE UNION SELECT 2 AS VALUE) AS T1).

I would go with the solution provided by kcrumley Just modify it slightly to handle NULLs create function dbo. GherArgumentOrNull(@val1 int, @val2 int) returns int as begin if @val1 >= @val2 return @val1 if @val1 NULL or x.

1 Nulls are important. And it's important to handle them consistently. The only proper answer to Is NULL > x is NULL.

– Mark Brackett Oct 10 '08 at 0:27 You are right, I will modify my answer to reflect that, thanks for pointing that out – kristof Oct 13 '08 at 7:48 If we pass an int and a NULL then I think it's more common to want the non-null value returned, so the function is acting as a combination of Max(x,y) and ISNULL(x,y). Hence I personally would change the last line to be: return ISNULL(@val1, @val2) - which admittedly is probably what you had to start with :) – locster Oct 26 '09 at 14:17 @the-locster, see comment by Mark – kristof Jan 17 '10 at 1:32 @the-locster, I agrre it is more common, but see the comment by Mark, nulls are important and as he points out "The only proper answer to Is NULL > x is NULL" – kristof Jan 17 '10 at 1:34.

The other answers are good, but if you have to worry about having NULL values, you may want this variant: SELECT o. OrderId, CASE WHEN ISNULL(o. NegotiatedPrice, o.

SuggestedPrice) > ISNULL(o. SuggestedPrice, o. NegotiatedPrice) THEN ISNULL(o.

NegotiatedPrice, o. SuggestedPrice) ELSE ISNULL(o. SuggestedPrice, o.

NegotiatedPrice) END FROM Order o.

Oops, I just posted a dupe of this question... The answer is, there is no built in function like Oracle's Greatest, but you can achieve a similar result for 2 columns with a UDF, note, the use of sql_variant is quite important here. Create table #t (a int, be int) insert #t select 1,2 union all select 3,4 union all select 5,2 -- option 1 - A case statement select case when a > be then a else be end from #t -- option 2 - A union statement select a from #t where a >= be union all select be from #t where be > a -- option 3 - A udf create function dbo. GREATEST ( @a as sql_variant, @b as sql_variant ) returns sql_variant begin declare @max sql_variant if @a is null or @b is null return null if @b > @a return @b return @a end select dbo.

GREATEST(a,b) from #t kristof Posted this answer: create table #t (id int IDENTITY(1,1), a int, be int) insert #t select 1,2 union all select 3,4 union all select 5,2 select id, max(val) from #t unpivot (val for col in (a, b)) as unpvt group by id.

Oct 13 '08 at 11:41 You should be careful when using sql_variant. Your function will give an unexpected result in the following situation: SELECT dbo. Greatest(CAST(0.5 AS FLOAT), 100) – Neil Jun 2 '11 at 15:43.

Sub Queries can access the columns from the Outer query so you can use this approach to use aggregates such as MAX across columns. (Probably more useful when there is a greater number of columns involved though) ;WITH Order AS ( SELECT 1 AS OrderId, 100 AS NegotiatedPrice, 110 AS SuggestedPrice UNION ALL SELECT 2 AS OrderId, 1000 AS NegotiatedPrice, 50 AS SuggestedPrice ) SELECT o. OrderId, (SELECT MAX(price)FROM (SELECT o.

NegotiatedPrice AS price UNION ALL SELECT o. SuggestedPrice) d) AS MaxPrice FROM Order o.

Nice! It scales up very well. – macias Jan 11 '11 at 9:52.

For the answer above regarding large numbers, you could do the multiplication before the addition/subtraction. It's a bit bulkier but requires no cast. (I can't speak for speed but I assume it's still pretty quick) SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2)) Changes to SELECT @val1*0.5+@val2*0.5 + ABS(@val1*0.5 - @val2*0.5) at least an alternative if you want to avoid casting.

You can do something like this: select case when o. NegotiatedPrice > o. SuggestedPrice then o.

NegotiatedPrice else o. SuggestedPrice end.

SELECT o. OrderID CASE WHEN o. NegotiatedPrice > o.

SuggestedPrice THEN o. NegotiatedPrice ELSE o. SuggestedPrice END AS Price.

I probably wouldn't do it this way, as it's less efficient than the already mentioned CASE constructs - unless, perhaps, you had covering indexes for both queries. Either way, it's a useful technique for similar problems: SELECT OrderId, MAX(Price) as Price FROM ( SELECT o. OrderId, o.

NegotiatedPrice as Price FROM Order o UNION ALL SELECT o. OrderId, o. SuggestedPrice as Price FROM Order o ) as A GROUP BY OrderId.

CREATE FUNCTION dbo. FnMax (@p1 INT, @p2 INT) RETURNS INT AS BEGIN DECLARE @Result INT SET @p2 = COALESCE(@p2, @p1) SELECT @Result = ( SELECT CASE WHEN @p1 > @p2 THEN @p1 ELSE @p2 END ) RETURN @Result END.

In its simplest form... CREATE FUNCTION fnGreatestInt (@Int1 int, @Int2 int ) RETURNS int AS BEGIN IF @Int1 >= ISNULL(@Int2,@Int1) RETURN @Int1 ELSE RETURN @Int2 RETURN NULL --Never t END.

The following code implements solution for the maximum of multiple variables: CREATE TABLE #TEMP (X int) INSERT #TEMP VALUES(@X1) INSERT #TEMP VALUES(@X2) INSERT #TEMP VALUES(@X3) INSERT #TEMP VALUES(@X4) ... INSERT #TEMP VALUES(@Xn) SELECT MAX(X) FROM #TEMP.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.