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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy