July 2, 2004 at 5:31 pm
In Excel, MAX(value_1, value_2, value_n) returns the highest value on the list. Is there a similar function in SQL Server?
July 2, 2004 at 6:05 pm
Are the values in a table? There is a max() function. Look under aggregates in BOL.
July 2, 2004 at 7:42 pm
The values are in multiple columns in a table. I need a function that compares multiple values and determines the max value. I know I'll probably end up writing my own function but I thought I'd check in and see if anyone has encountered the same issue.
Pete
July 3, 2004 at 7:48 am
nothing for multiple columns.
July 5, 2004 at 4:28 am
You cannot Compare two columns unless you write some program in sql server. You can achive teh same by writing a UDF
--------------------------------
July 5, 2004 at 6:27 am
If all columns the same datatype, ugly, poor performance but maybe ...
SELECT MAX(maxval) as [maxval]
FROM (
SELECT MAX(col3) as [maxval] FROM
UNION
SELECT MAX(col4) as [maxval] FROM
UNION
SELECT MAX(col7) as [maxval] FROM
UNION
SELECT MAX(col8) as [maxval] FROM
) a
Far away is close at hand in the images of elsewhere.
Anon.
July 5, 2004 at 3:57 pm
Maybe you're looking for something like this ?
Create Function MaxFromList (@String nvarchar(4000), @delimiter varchar(1) = ',')
returns nvarchar(255)
AS
Begin
declare @Datalength int
declare @CurrentPosition int
declare @NextPosition int
declare @ThisTable table(Value nvarchar(255))
declare @MaxWord nvarchar(255)
set @CurrentPosition = 1
set @Datalength = LEN(@String)
while( @CurrentPosition <= @Datalength)
begin
set @NextPosition =
case CharIndex(@delimiter,@String,@CurrentPosition)
when 0 then @DataLength + 1
else CharIndex(@delimiter,@String,@CurrentPosition)
end /* case */
insert into @ThisTable select cast(substring(@string,@Currentposition, (@NextPosition - @CurrentPosition)) as nvarchar(255))
set @CurrentPosition = @NextPosition + 1
end /* while */
select @MaxWord = max(Value) from @ThisTable
return @MaxWord
end /* function body */
Call it like this:
select dbo.MaxFromList('1,22,33,456.55,3,4',default)
Result = 456.55
or
select dbo.MaxFromList('anni-brian-Joan','-')
result = Joan
July 7, 2004 at 11:07 am
Thanks, Rene, ALL.
This solution should work nicely.
P
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply