Function in SQL Server 2008 similar to GREATEST in mysql?

Question

I want to find the maximum value of multiple columns.

MySQL supports the GREATEST function but SQL Server doesn't.

Is there any function similar to this in SQL Server 2008?

Please also look at http://dba.stackexchange.com/questions/21542/what-is-the-most-efficient-way-to-get-the-minimum-of-multiple-columns-on-sql-ser — Aaron Bertrand, Aug 13 '12 at 13:59

Martin Smith · Answer 1 · 2023-01-02T17:49:02.710

22

Not in SQL Server 2008 but GREATEST and LEAST functions are now available for SQL Server 2022 and cloud versions of SQL Server

For previous versions you can use the fact a sub query can access the columns from the outer query so you can add a sub query Selecting the max from a union of those.

SELECT *, 
      (SELECT MAX(c) FROM (VALUES(number),(status)) T (c)) AS Greatest
FROM master..spt_values

Or for SQL Server 2000/2005

SELECT *, 
      (SELECT MAX(c) FROM 
                    (SELECT number AS c 
                     UNION ALL 
                     SELECT status) T) AS GreatestNumberOrStatus
FROM master..spt_values

edited Jan 02 '23 at 17:49

answered Jan 18 '11 at 15:25

Martin Smith

438,706
87
741
845

2

+1, though it is less efficient than a bunch of `CASE` statements. – Quassnoi Jan 18 '11 at 15:30
Agreed but it isn't catastrophically inefficient and easier to maintain if the number of columns is large. – Martin Smith Jan 18 '11 at 15:32
"you can add a sub query UNION ALL ing the columns of interest as a derived table" -- could do the same using a CTE. – onedaywhen Jan 18 '11 at 15:45
@oneday - I'm not sure how? The derived table only contains the values for the current row. – Martin Smith Jan 18 '11 at 15:48
2

++ for the VALUES() syntax for 2008 – RichardTheKiwi Jan 18 '11 at 20:36
@Quassnoi: Can you provide an example with a bunch of `CASE` statements? I feel that the `CASE` statement size explodes with `O(2^n)` when `n` is the number of parameters to `GREATEST`... – Lukas Eder Jan 08 '12 at 14:31
+1000 for VALUES syntax! True, null-friendly, n-ary Greatest/Least() functionality! Agree with @Lukas about O(2ⁿ); plus, if nullable values, having to pick either a magic value for isnull (yuck), or expanding the syntax further with "OR val(n) is null". – bwperrin Feb 08 '17 at 21:43

score 5 · Answer 2 · answered Jan 19 '11 at 03:36

5

For this, I created a scalar function as follows:

CREATE FUNCTION [dbo].[MaxOrNull](@val1 int, @val2 int)
returns int
as
begin
    if @val1 >= @val2 RETURN @val1
    if @val1 < @val2 RETURN @val2

    RETURN NULL
end

It's the most elegant solution and can be used anywhere in your SQL code.

answered Jan 19 '11 at 03:36

IamIC

17,747
20
91
154

1

I'm not sure I've ever seen the words scalar function and elegant in the same sentence before! How would the OP use this to calculate `GREATEST(col1,col2,col3,col4,col5,col6)` for example? Wouldn't it need a lot of permutations? Edit: Actually you could just nest the calls of course in this case where the permutations grow is the different scenario where you want to order across columns. – Martin Smith Jan 19 '11 at 11:27
Well @Martin, sometimes it takes a shift of viewpoint to see elegance. Hard coding messy SQL all over the place like the verbose answer in the top answer ^^^ above is just crazy. You answered your second question. The same would hold true in C++ or C#. – IamIC Jan 19 '11 at 13:51
2

A stretch for me to give you +1. But this is the approach I have used before SQL 2008, in preference over nested select-max-union. The function I normally use is more concise `create function dbo.greater(@a int, @b int) returns int as begin return case when @a >= isnull(@b,@a) then @a else @b end end` – RichardTheKiwi Jan 19 '11 at 20:34
I like your CASE version. Very tidy. +1 – IamIC Jan 19 '11 at 20:38

score 3 · Answer 3 · edited Nov 18 '14 at 09:41

3

I would recommend the following solution:

SELECT (CASE WHEN t.createdt < t.changedt THEN t.changedt ELSE t.created END) AS ChgDate
  FROM table t

edited Nov 18 '14 at 09:41

ekostadinov

6,880
3
29
47

answered Nov 18 '14 at 09:22

user4264784

31
1

score 1 · Answer 4 · edited Nov 17 '15 at 17:25

A possible solution:

Create FUNCTION [dbo].[MaxOf]
    (
      @val1 INT =0,
      @val2 INT=0 ,
      @val3 INT =0,
      @val4 INT =0,
      @val5 INT =0,
      @val6 INT =0,
      @val7 INT =0,
      @val8 INT =0,
      @val9 INT =0,
      @val10 INT =0,
      @val11 INT =0,
      @val12 INT =0,
      @val13 INT =0,
      @val14 INT =0,
      @val15 INT =0,
      @val16 INT =0,
      @val17 INT =0,
      @val18 INT =0,
      @val19 INT =0,
      @val20 INT  =0)
      --OUTPUT 
     RETURNS INT  WITH SCHEMABINDING
AS  
   BEGIN
        DECLARE  @MAX AS INT ;
        SET @MAX=0
        IF isnull(@val1,0)> isnull(@MAX,0) SET @MAX=isnull(@val1,0) 
        IF isnull(@val2,0)> isnull(@MAX,0) SET @MAX=isnull(@val2,0) 
        IF isnull(@val3,0)> isnull(@MAX,0) SET @MAX=isnull(@val3,0) 
        IF isnull(@val4,0)> isnull(@MAX,0) SET @MAX=isnull(@val4,0) 
        IF isnull(@val5,0)> isnull(@MAX,0) SET @MAX=isnull(@val5,0) 
        IF isnull(@val6,0)> isnull(@MAX,0) SET @MAX=isnull(@val6,0) 
        IF isnull(@val7,0)> isnull(@MAX,0) SET @MAX=isnull(@val7,0) 
        IF isnull(@val8,0)> isnull(@MAX,0) SET @MAX=isnull(@val8,0) 
        IF isnull(@val9,0)> isnull(@MAX,0) SET @MAX=isnull(@val9,0) 
        IF isnull(@val10,0)> isnull(@MAX,0) SET @MAX=isnull(@val10,0) 
        IF isnull(@val11,0)> isnull(@MAX,0) SET @MAX=isnull(@val11,0) 
        IF isnull(@val12,0)> isnull(@MAX,0) SET @MAX=isnull(@val12,0) 
        IF isnull(@val13,0)> isnull(@MAX,0) SET @MAX=isnull(@val13,0) 
        IF isnull(@val14,0)> isnull(@MAX,0) SET @MAX=isnull(@val14,0) 
        IF isnull(@val15,0)> isnull(@MAX,0) SET @MAX=isnull(@val15,0) 
        IF isnull(@val16,0)> isnull(@MAX,0) SET @MAX=isnull(@val16,0) 
        IF isnull(@val17,0)> isnull(@MAX,0) SET @MAX=isnull(@val17,0) 
        IF isnull(@val18,0)> isnull(@MAX,0) SET @MAX=isnull(@val18,0) 
        IF isnull(@val19,0)> isnull(@MAX,0) SET @MAX=isnull(@val19,0) 
        IF isnull(@val20,0)> isnull(@MAX,0) SET @MAX=isnull(@val20,0) 

        RETURN @MAX ;
    END

and the call would be

SELECT dbo.MaxOf (2,3,4,0,0,0,0,200,8,0,0,0,0,0,0,0,0,0,0,0)

score 1 · Answer 5 · edited Sep 27 '17 at 19:48

1

Try using TOP instead or MAX.

edited Sep 27 '17 at 19:48

Arun Vinoth-Precog Tech - MVP

22,364
14
59
168

answered Jan 18 '11 at 15:25

VoodooChild

9,776
8
66
99

Function in SQL Server 2008 similar to GREATEST in mysql?

5 Answers5

Linked

Related