Need max() function by record not column
-
I need a function like max(column1, column2, column3,...)
Is there such thing in SQL Server ? I did not find anything like this in BOL. I can manipulate it with case when column2>column1 then... end, but is there a more elegant solution ?
Thanks
-
If there is I'd be interested to see it. I've had a similar problem before and used the case statement. You could write a function to do it that accepts multiple values but I think you'd end up using the case statement in that as well.
-
SELECT PK, MAX(Col)
FROM (SELECT PK, Col1 AS Col FROM Table1 UNION ALL
SELECT PK, Col2 FROM Table1 UNION ALL
SELECT PK, Col3 FROM Table1 UNION ALL
SELECT PK, Col4 FROM Table1
) AS x
GROUP BY PK
ORDER BY PK
N 56°04'39.16"
E 12°55'05.25" -
create function dbo.mymax (
@i1 int ,
@i2 int = null ,
@i3 int = null ,
@i4 int = null ,
@i5 int = null ,
@i6 int = null ,
@i7 int = null ,
@i8 int = null ,
@i9 int = null ,
@i10 int = null )
returns int
as
begin
declare @x table ( i int null )
insert @x
select @i1
insert @x
select @i2
insert @x
select @i3
insert @x
select @i4
insert @x
select @i5
insert @x
select @i6
insert @x
select @i7
insert @x
select @i8
insert @x
select @i9
insert @x
select @i10
return ( select max (i) from @x where i is not null )
end
go
select dbo.mymax (1,3,4,6,3,2,4,1,5,7)
go
customize as per your needs ...
-
Thanks Peter, I liked your solution. I already implemented it for my situation.
-
Thanks Peter. Solved a problem I was having.
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply