Get max-value of columns

Hi
Is there an easy way to get the MaxVal of some Columns
SELECT ID, MaxVal( Value1, Value2, Value3, ... , Value13) AS BestBet
FROM ...
With only 2 columns I could just compare the values an return the max
but with an increasing number of colums ...
any ideas
thx Peter
Your requirement indicates to me that this table isn't in correctly
normalized. It rarely makes sense to take a maximum accross *different*
attributes in a table (dates are sometimes an exception). The fact that you
want to do so suggests that the columns Value1 .. Value13 may be a repeating
group (the *same* attribute in different columns). That's always a weak
design for a table so a better solution may be to redesign it. If that isn't
the case then here's one query to get the maximum across columns:
SELECT id,
(SELECT MAX(val)
FROM
(SELECT value1 AS val UNION ALL
SELECT value2 UNION ALL
SELECT value3
...
) AS X) bestbet
FROM YourTable
or, if you need to handle NULLs by returning BestBet as NULL when any value
is NULL:
SELECT id,
(SELECT MAX(val)
FROM
(SELECT value1 AS val UNION ALL
SELECT value2 UNION ALL
SELECT value3
) AS X
HAVING COUNT(*)=COUNT(val)) bestbet
FROM YourTable
David Portas
SQL Server MVP
|||One approach would be to create a scalar function. You need to define the
maximum number of values supported for comparison as input paramters, but it
would be flexible. An issue is that you always have to pass in all
parameters to the function, so a null placeholder is needed.
CREATE FUNCTION Greatest (
@.p1 int=0
, @.p2 int=0
, @.p3 int=0
, @.p4 int=0)
RETURNS int
AS
BEGIN
declare @.t1 table (c1 int), @.r1 int
insert @.t1
select @.p1
union all
select @.p2
union all
select @.p3
union all
select @.p4
select @.r1 = max(c1) from @.t1
RETURN @.r1
END
GO
SELECT dbo.Greatest
(1,
2,
null,
null)
Regards,
Carl
"Peter Plumber" <Klempner@.gmxdot.net> wrote in message
news:%23RSe6MUqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there an easy way to get the MaxVal of some Columns
> SELECT ID, MaxVal( Value1, Value2, Value3, ... , Value13) AS BestBet
> FROM ...
> With only 2 columns I could just compare the values an return the max
> but with an increasing number of colums ...
> any ideas
> thx Peter
>