TSQL Max in a Range

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 lik开发者_开发知识库e 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.

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.

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

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.

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