July 25, 2013 at 3:57 am
Hi,
in a SELECT, I would like to have a function which returns the minimum of dynamic list of values, like this:
select GetMin(4,3) => returns 3
select GetMin(4,3,1) => returns 1
select GetMin(2,5,0,null,7) => returns 2
Do you have an idea, how to code the dynamic parameter list of GetMin?
It's like COALSECE, is there a source code available of this build-in function?
thank you for your help!
Ralf
July 25, 2013 at 7:59 am
The same question from past!
You will see my response there too...
http://www.sqlservercentral.com/Forums/Topic1305828-391-1.aspx
July 25, 2013 at 8:32 pm
Although the syntax is not the same, you could DECLARE a TYPE that is a TABLE and pass that into the FUNCTION.
You then just assign each value to a row of the TABLE VARIABLE you create from the TYPE.
There is a restriction I believe though, that you can't pass a sub-query (that results in a table with multiple rows) to the FUNCTION.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2013 at 10:21 pm
Ralf (7/25/2013)
Hi,in a SELECT, I would like to have a function which returns the minimum of dynamic list of values, like this:
select GetMin(4,3) => returns 3
select GetMin(4,3,1) => returns 1
select GetMin(2,5,0,null,7) => returns 2
Do you have an idea, how to code the dynamic parameter list of GetMin?
It's like COALSECE, is there a source code available of this build-in function?
thank you for your help!
Ralf
I don't have SSMS available at the moment so can't give a tested solution, but I'll take a stab at it.
This function splits the string and then the select returns the MIN value. I use -1 to trap any nulls and thus return null as the min value if any exists. If your data includes negative numbers or you don't want nulls to be counted you will have to change the method accordingly,
DECLARE @teststr VARCHAR(8000)
SET @teststr = '2,5,0,null,7'
SELECT DISTINCT --or maybe TOP(1)?
NULLIF(ISNULL(MIN(dsk.Item),-1),-1) AS minItem
FROM
dbo.DelimitedSplit8K(@teststr,'.') AS dsk
You might also consider using a RANK operator instead of MIN(). You'd need to run some performancce tests and look at the query plan to see which is better in your situation.
July 25, 2013 at 10:30 pm
Steven - Your posting gave me the idea for this (your Tally FUNCTION):
CREATE FUNCTION [dbo].[itvfTally]
(
@pMin BIGINT
,@pMax BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
/* Uses the Bates algorithm for generating the rows from */
/* http://www.sqlservercentral.com/scripts/Tally+Table/99617/ */
/*
Usage:
SELECT N FROM [dbo].[itvfTally_Original](1,10000)
SELECT N FROM [dbo].[itvfTally_Original](20001,30000)
SELECT N FROM [dbo].[itvfTally_Original](-999,1000)
*/
WITH T1(F) AS
(
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
T2(F) AS
(SELECT 1 FROM T1 A, T1 B),
T3(F) AS
(SELECT 1 FROM T2 A, T2 B),
T4(F) AS
(SELECT 1 FROM T3 A, T3 B),
cteTally(N) AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM T4
)
SELECT
N
FROM
cteTally T
WHERE
T.N BETWEEN @pMin AND @pMax;
GO
SELECT MIN(N)
FROM itvfTally(0,1000)
WHERE N IN (4,3)
SELECT MIN(N)
FROM itvfTally(0,1000)
WHERE N IN (4,3,1)
SELECT MIN(N)
FROM itvfTally(0,1000)
WHERE N IN (2,5,0,null,7)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2013 at 11:33 pm
I like it! I wonder if calculating the min and max values and passing them in as variables would adversely effect performance? That would (or could} keep the tally table rows at a minimum. Otherwise, I think dealing with NULLs might be the one issue to deal with.
Do you think a CROSS APPLY would work? That would avoid having to hard-code the min/max values at all,
. (I don't have SQL available at the moment so can't test.)
DECLARE @teststr VARCHAR(8000)
SET @teststr = '4,3,1'
SELECT t,N
FROM itvfTally(MIN(d.Item),MIN(d.Item)) AS t
CROSS APPLY
DelimitedSplit8K(@teststr,',') AS d
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply