TSQL Max in a Range

Question

I've been having an interesting conversation with some coworkers about how to solve the following problem. Suppose I have a table with the following columns:

foo_1,foo_2, foo_3, field_etc,
Score1, Score2, Score3, Score4, Score5,
MoreFields, MoreScores, EvenMoreScores

I need to build a query which returns the following:

foo_1, foo_2, foo_3, field_etc, MaxScore

Where MaxScore is the maximum value between Score1, Score2, Score3, Score4, Score5

What we have is something like this:

SELECT
  foo_1, foo_2, foo_3, field_etc,
  (
    SELECT MAX(foo) as Something
    FROM
    (
      SELECT SomeTable.Score1 AS foo
      UNION ALL
      SELECT     SomeTable.Score2
      UNION ALL
      SELECT     SomeTable.Score3
      UNION ALL
      SELECT     SomeTable.Score4
      UNION ALL
      SELECT     SomeTable.Score5
    ) AS x
  ) AS MaxScore
FROM
  SomeTable

I feel like there should be a cleaner way to do this but we have not found one. We are avoiding the obvious UDF solution for performance reasons.

Would be great if there were some kind of range function in tsql so I could do:

SELECT
  foo_1, foo_2, foo_3, field_etc, MAX(Score1, Score2, Score3, Score4, Score5) as MaxScore
FROM
  SomeTable

Ideally the underlying table would be normalized and this wouldn't be a problem but I don't have the ability to change it here.

score 3 · Accepted Answer · answered Mar 01 '11 at 19:17

3

Given that specific problem, you should be able to use an unpivot. I may have bugs and typos in here, but concept is sound:

SELECT foo1, foo2, foo3, field_etc, max(Score) MaxScore
 from (select foo1, foo2, foo3, field_etc, Score1, Score2, Score3, Score4, Score5
        from SomeTable) base
  unpivot (Score
           for WhichScore
           in (Score1, Score2, Score3, Score4, Score5)) upvt
  group by foo1, foo2, foo3, field_etc

If you don't know the names of the columns (Score1, Score2, etc.) it gets ugly since you'd have to use dynamic SQL.

answered Mar 01 '11 at 19:17

Philip Kelley

39,426
11
57
92

Good stuff. I like this solution allot. It is much more obvious what is going on and prevents us from having to use a UDF. It works as stated. Thanks! – John Mar 01 '11 at 19:57
@John: If you care about performance you need to test this. I guess what you got is faster and that the cte version is pretty much the same as your original. – Mikael Eriksson Mar 01 '11 at 20:15
In my testing, this was by far faster. Original was about 8 seconds and this was under a second. – John Mar 01 '11 at 21:10

score 2 · Answer 2 · edited May 23 '17 at 11:47

2

There is no built-in function in MSSQL (others do, Oracle has Greatest for example) but you can a function that does what you want:
Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

You only need a user defined function that takes two parameters and returns the maximum, lets Call its BIGGEST, the you can do like this:

SELECT
  foo_1, foo_2, foo_3, field_etc, 
  BIGGEST(BIGGEST(BIGGEST(BIGGEST(Score1, Score2), Score3), Score4), Score5) as MaxScore
FROM SomeTable

edited May 23 '17 at 11:47

Community

1
1

answered Mar 01 '11 at 18:54

ntziolis

10,091
1
34
50

I probably should have mentioned that we are avoiding the UDF solution due to performance reasons. UDFs executed per row in SqlServer are slow. – John Mar 01 '11 at 18:59
Then you are out of luck, you will need to either use unions or an if block. There is a solution making use of the `ABS` which works well for 2 values, but I think this might grow quite large and impractical when used with 5 values, check out the second answer in the question that I have linked. – ntziolis Mar 01 '11 at 19:03
Sry instead of `IF` I meant `CASE`, you can get an example here: http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns – ntziolis Mar 01 '11 at 19:11

score 0 · Answer 3 · answered Mar 01 '11 at 19:00

0

I've been using this:

CREATE FUNCTION [GreaterOf2INTs]
  (@a int, @b int)
    RETURNS INT
AS
BEGIN
  DECLARE @greater INT

 IF @a > @b SELECT @greater = @a
 ELSE SELECT @greater = @b

 RETURN @greater
END

You could use it nested several times. Ugly, but it works. It's faster than UNIONs too.

answered Mar 01 '11 at 19:00

John Pick

5,562
31
31

Now I see the OP won't use UDFs. – John Pick Mar 01 '11 at 19:00
Sorry, I added that when I edited it after someone else suggested it. – John Mar 01 '11 at 20:59

score 0 · Answer 4 · answered Mar 01 '11 at 19:51

Don't know if this is any better than what you already got but it is at least different.

;with cte1 as
(
  select
    foo_1, foo_2, foo_3, field_etc,
    case when Score1 > Score2 then Score1 else Score2 end as Score12,
    case when Score3 > Score4 then Score3 else Score4 end as Score34,
    Score5
  from SomeTable
),
cte2 as
(
  select
    foo_1, foo_2, foo_3, field_etc,
    case when Score12 > Score34 then Score12 else Score34 end as Score1234,
    Score5
  from cte1
),
cte3 as
(
  select
    foo_1, foo_2, foo_3, field_etc,
    case when Score5 > Score1234 then Score5 else Score1234 end as MaxScore
  from cte2
)
select *
from cte3

TSQL Max in a Range

4 Answers4