greatest(a,b,...) function
-
MySQL, oracle, and postgres all seem to support a function called GREATEST which is passed several parameters and returns the largest value between them, but i can't find an equivalent function in microsoft SQL server. For example, GREATEST(4,42) will return 42.
i'd rather not use
CASE WHEN (a>b) THEN a ELSE b END
because "b" is going to be a subselect statement (returning a single integer field) that i dont want to duplicate
Thanks for any help
-
You could off course create your own user defined function.
There are many examples at SSC.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I thought this would be "prettier" than it actually is because I didn't realize that unlike stored procedures, optional parameters in functions can't actually be ignored. But this might give you some ideas about how to implement what you are looking for:
if object_id('Greatest_') is not null
drop function Greatest_
go
create function dbo.Greatest_ (@parm1_ as int, @parm2_ as int, @parm3_ as int = NULL, @parm4_ as int = NULL)
returns int
as
begin
declare @i_ as int
select @i_ = max(val_)
from (select @parm1_ as val_ union all
select @parm2_ as val_ union all
select @parm3_ as val_ union all
select @parm4_ as val_) t
where val_ is not null
return @i_
end
go
--this is where it's a little ugly, you have to add DEFAULT to each parameter position you don't want to use.
select dbo.greatest_(1,2,3,default)
James
-
James: your function worked. i tried to modify it so that it actually could accept a variable number of parameters by passing a single varchar containing all values separated with a delimiter (here i just used a comma), but to make the end work, i had to make a call to sp_executesql and because of that, it gave me the following error: "Only functions and extended stored procedures can be executed from within a function."
Here's what i came up with, if anyone can think of a way around the sp_executesql call, it should work (this would be a lot simpler if there was a "split" function or function overloading in sql...):
if object_id('Greatest_') is not null drop function Greatest_
go
create function dbo.Greatest_(@paramlist as varchar(4000)) returns int as
begin
declare @delimiter as varchar(10)
declare @last_index as int
declare @next_index as int
declare @sql_to_exec as nvarchar(4000)
declare @i as int
set @delimiter=','
set @last_index=1
set @next_index=charIndex(@delimiter,@paramlist,@last_index)
set @sql_to_exec=''
while @next_index>0 begin
if @sql_to_exec''
set @sql_to_exec=@sql_to_exec+' UNION ALL '
set @sql_to_exec=@sql_to_exec+'select cast('+
left(right(@paramlist,len(@paramlist)-@last_index+1),@next_index-@last_index)+
' as int) as val_'
set @last_index=@next_index+len(@delimiter)
set @next_index=charIndex(@delimiter,@paramlist,@last_index)
end
if @sql_to_exec''
set @sql_to_exec=@sql_to_exec+' UNION ALL '
set @sql_to_exec=@sql_to_exec+'select cast('+right(@paramlist,len(@paramlist)-@last_index+1) +' as int) as val_'
set @sql_to_exec=N'set @i=(select max(val_) from ('+@sql_to_exec+') t where val_ is not null)'
EXEC sp_executesql @sql_to_exec,N'@i int OUTPUT',@i OUTPUT
return @i
end
go
select dbo.Greatest_('24,42')
-
While I generally shy away from tokenized string parameters, the following will do what you want without resorting to the sp_exectuesql.
NOTE: I obtained the "split" function from a script posted on this web site (reference/author identified in the code).
IF exists (SELECT * from dbo.sysobjects
WHERE id = object_id(N'[dbo].[Split]')
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[Split]
GO
CREATE FUNCTION dbo.Split ( @vcDelimitedString varchar(8000),
@vcDelimiter varchar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString - The string to be split
@vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------
***************************************************************************/
RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1), --Array index
Element varchar(1000) --Array element contents
)
AS
BEGIN
DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
GO
--This is back to my original code, adjusted to use a tokenized string parameter
--and calling the split function to get a table of the values
if object_id('Greatest_') is not null
drop function Greatest_
go
create function dbo.Greatest_ (@parmlist_ as varchar(4000))
returns int
as
begin
declare @i_ as int
select @i_ = max(element)
from dbo.split(@parmlist_,',') t
return @i_
end
go
select dbo.greatest_('24,25')
-
While I wouldn't find it a necessarily aesthetically pleasing solution, what about splitting the delimited list, placing it into a table variable, and returning the max() (or min to emulate Least) from the table? That should handle a variable number of parameters, as well as eliminate the need for dynamic SQL.
Edit: I notice that JLK did what I was speaking of, including the code for you, while I was typing my post.
-
well, i'm impressed, but it's getting a bit more complicated than i originally had in mind. : /
think it'll be easiest to go with JLK's original solution, but the other one is definitely getting saved in case needed in the future.
thanks to all who replied
-
The nice thing about the second solution is you can implemnt the "Least" function with only a couple of additonal lines of code:
create function dbo.Least_ (@parmlist_ as varchar(4000))
returns int
as
begin
declare @i_ as int
select @i_ = min(element_)
from dbo.split(@parmlist_,',') t
return @i_
end
go
James
-
Correct me if I'm wrong, but if the function that JLK proposed were to be inserted into an otherwise set-based UPDATE or SELECT statement, would it not then be executed as a non-set-based or "row by row" command?
I was hoping for a solution that wouldn't scuttle the set-based ship. Anyone?
-
Greg J (11/29/2011)
Correct me if I'm wrong, but if the function that JLK proposed were to be inserted into an otherwise set-based UPDATE or SELECT statement, would it not then be executed as a non-set-based or "row by row" command?I was hoping for a solution that wouldn't scuttle the set-based ship. Anyone?
Well, this is a 4-year-old thread, but . . .
You could implement the same logic as an inline table-valued function and use APPLY to call it, rather than calling a scalar function in the list of columns in the SELECT list (which will result in a separate call for each row).
CREATE FUNCTION dbo.Least (@parmlist varchar(4000)
RETURNS TABLE
AS
RETURN
(
SELECT MIN(ELEMENT) AS least
FROM dbo.Split(@parmlist, ',')
)
Jason Wolfkill
-
wolfkillj (11/30/2011)
Greg J (11/29/2011)
Correct me if I'm wrong, but if the function that JLK proposed were to be inserted into an otherwise set-based UPDATE or SELECT statement, would it not then be executed as a non-set-based or "row by row" command?I was hoping for a solution that wouldn't scuttle the set-based ship. Anyone?
Well, this is a 4-year-old thread, but . . .
You could implement the same logic as an inline table-valued function and use APPLY to call it, rather than calling a scalar function in the list of columns in the SELECT list (which will result in a separate call for each row).
CREATE FUNCTION dbo.Least (@parmlist varchar(4000)
RETURNS TABLE
AS
RETURN
(
SELECT MIN(ELEMENT) AS least
FROM dbo.Split(@parmlist, ',')
)
Oh, and by the way, DO NOT use the delimited-string splitter function posted by JLK. It WILL kill performance. Use the one described here:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
As the author, Jeff Moden, would caution you, don't use this code in production until you understand how it works!
Jason Wolfkill
-
HI James,:-)
select dbo.greatest_('1099,1,2,3,9,10') greatestsurprisingly the Result is 9
However logically the return value should be 1099.
even for below results are surprise
select dbo.greatest_('1199,989') greatest, dbo.greatest_('1199,9') greatest1result should be 1199,119 however returned 989,9
Can you suggest fix for this?
I hope converting to varchar will fix it, what is your say?
--Rakesh 🙂
-
rakesh1084 (8/22/2012)
HI James,:-)select dbo.greatest_('1099,1,2,3,9,10') greatestsurprisingly the Result is 9
However logically the return value should be 1099.
even for below results are surprise
select dbo.greatest_('1199,989') greatest, dbo.greatest_('1199,9') greatest1result should be 1199,119 however returned 989,9
Can you suggest fix for this?
I hope converting to varchar will fix it, what is your say?
--Rakesh 🙂
Change the function to cast the values as a numeric (or integer) value. You are getting just what you should based on the fact that the values are character strings not numeric values.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply