10

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

In Excel, there's a function called "MAX" that accepts numbers and returns the largest one in the set. Is there a function in T-SQL that duplicates this functionality? I haven't been able to find one, and I've written a UDF that does it for me, but I thought it was worth asking.

Here is the function I've been using:

CREATE FUNCTION dbo.LargerOf
(
    -- Add the parameters for the function here
    @First FLOAT,
    @Second FLOAT
)
RETURNS FLOAT
AS
BEGIN

    DECLARE @Result FLOAT

    IF @First > @Second
        SET @result = @First
    ELSE
        SET @Result = @Second

    RETURN @Result

END
GO

I don't expect any luck, but instead of moving my function to a whole bunch of new servers, I thought I'd at least ask. Thanks!

Community
  • 1
  • 1
SqlRyan
  • 33,116
  • 33
  • 114
  • 199
  • You actually want a tsql equivalent of the MySQL/Oracle function GREATEST. Don't know any other than stored procedure :( – instanceof me Jun 12 '09 at 22:54
  • That is, indeed, exactly what I want. Searching using that function name only re-affirms that I'm out of luck. Thanks! – SqlRyan Jun 12 '09 at 23:03

4 Answers4

10

I don't know if the function you need exists, but for a workaround, I like this one better

set @max = case when @first > @second then @first else @second end
tekBlues
  • 5,745
  • 1
  • 29
  • 32
  • At first I was doing this, but then I got tired of typing it out (since I had to use it constantly) and decided to essentially wrap it in a UDF, which is the code I've given above. Thanks for the suggestion, though! – SqlRyan Jun 12 '09 at 23:00
  • It will make a smaller UDF anyway :-) – tekBlues Jun 12 '09 at 23:02
  • 2
    Be careful with using a UDF for this. Sometimes simply encapsulating code in a UDF will cause performance issues because SQL Server has to act on the resultset on a row-by-row basis, whereas built-in functions can usually be used on a set-based basis. – Tom H Jun 13 '09 at 00:29
  • @TomH - Good point. This can be mitigated by using a table-valued function though. – Aaron Alton Jun 13 '09 at 01:57
  • @TomH - this is not a problem so long as the UDF is deterministic, as this one is. – Tor Haugen Aug 20 '09 at 21:00
6

You could use:

CASE
   WHEN @First >= @Second THEN @FIRST
   ELSE @Second
END
Tor Haugen
  • 19,509
  • 9
  • 45
  • 63
  • Across two different question pages on this I have checked, your solution seems to be the shortest, most logical, and easiest to implement. I can't comment on performance on hundreds-of-gigabytes-of-data, but it sure does the trick where I needed it. People who complain that this approach is too verbose need to embrace the 'Structured' aspect of SQL ;p – Darren Ringer Feb 04 '15 at 21:53
4

declare @first int, @second int

select @first=45, @second=123

select max(a) from (select @first a UNION ALL select @second) x

--OR

select max(a) from (values (@first),(@second)) x(a)

msi77
  • 1,602
  • 1
  • 11
  • 10
  • 1
    I like this way of doing it with UNION because if you need the max or min of a complicated calculation and a constant, you only have to write the calculation once. Makes for much cleaner code. – John Jun 28 '12 at 14:59
1

Unfortunately not.

A word of warning, for extremely intensive usage, I've found that scalar functions (even those which could be easily inlined with a CASE, like yours) really do not perform well on SQL Server 2005, so if you are dealing with millions of calls, put it inline (sometimes you can fake an inline TVF).

Hopefully, SQL Server will eventually have an inline SVF or have a function equivalent to GREATEST!

Cade Roux
  • 88,164
  • 40
  • 182
  • 265