April 24, 2008 at 6:36 am
Hi,
I'm looking for a function in T-SQL for getting the biggest or the smallest value between two or more columns (like GREATEST function of Oracle or MINDATE/MAXDATE in Firebird)
Can anyone please advise?
Thanks in advance,
D.
April 24, 2008 at 6:45 am
I'm not sure if what you're asking for is "max" and "min". Take a look at those in Books Online and see if they will do what you need.
If you want to return the higher of two values in two different columns, I think a Case statement is needed.
case
when Col1 => Col2 then Col1
when Col2 > Col1 then Col2
else null
end
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2008 at 8:06 am
Thanks for the fast reply. What I need is not the MAX and MIN. I need a function like COALESCE(field1, field2) which return one of the two fields. The snippet you post it should do the job.
Kind Regards,
D.
August 22, 2012 at 1:42 pm
Below two functions are very Similar to least and greatest functions in oracle.
1. SQL Code for least: -
GO
/****** Object: UserDefinedFunction [dbo].[least] Script Date: 08/23/2012 00:53:38 ******/
/*Author: Rakesh
Description: This works good for numbers and alphabet.
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[least] (@str1 nvarchar(max),@str2 nvarchar(max))
RETURNS nvarchar(max)
BEGIN
DECLARE @retVal nvarchar(max);
set @retVal = (select case when @str1<=@str2 then @str1 end as retVal)
RETURN @retVal;
END;
GO
Usage: -
select dbo.least(10,100) LEAST_OF_TWO, dbo.least('R','S') LEAST_OF_TWO_ALPHABET
2. SQL code for greatest: -
GO
/****** Object: UserDefinedFunction [dbo].[greatest] Script Date: 08/23/2012 01:00:56 ******/
/*Author: Rakesh
Description: This works good for numbers and alphabet.
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[greatest] (@str1 nvarchar(max),@str2 nvarchar(max))
RETURNS nvarchar(max)
BEGIN
DECLARE @retVal nvarchar(max);
set @retVal = (select case when @str1<=@str2 then @str2 end as retVal)
RETURN @retVal;
END;
GO
Usage: -
select dbo.greatest(10,100) GREATEST_OF_TWO, dbo.greatest('a','z') GREATEST_OF_TWO_ALPHABET
August 22, 2012 at 3:34 pm
GSquared (4/24/2008)
I'm not sure if what you're asking for is "max" and "min". Take a look at those in Books Online and see if they will do what you need.If you want to return the higher of two values in two different columns, I think a Case statement is needed.
case
when Col1 => Col2 then Col1
when Col2 > Col1 then Col2
else null
end
If the columns are nullable, then it gets complex fast.
Here is an example with 4 columns:
case
when a.Val1 is not null and
(a.Val1 >= a.Val2 or a.Val2 is null) and
(a.Val1 >= a.Val3 or a.Val3 is null) and
(a.Val1 >= a.Val4 or a.Val4 is null)
then a.Val1
when a.Val2 is not null and
(a.Val2 >= a.Val1 or a.Val1 is null) and
(a.Val2 >= a.Val3 or a.Val3 is null) and
(a.Val2 >= a.Val4 or a.Val4 is null)
then a.Val2
when a.Val3 is not null and
(a.Val3 >= a.Val1 or a.Val1 is null) and
(a.Val3 >= a.Val2 or a.Val2 is null) and
(a.Val3 >= a.Val4 or a.Val4 is null)
then a.Val3
when a.Val4 is not null and
(a.Val4 >= a.Val1 or a.Val1 is null) and
(a.Val4 >= a.Val2 or a.Val2 is null) and
(a.Val4 >= a.Val3 or a.Val3 is null)
then a.Val4
else null
end
This is an alternative that is easier to code when you have to do this for a large number of columns:
Select
[Max_of_Val1_to_Val4] =
(
select
X1= max(bb.xx)
from
(
select xx = a.Val1 where a.Val1 is not null union all
select xx = a.Val2 where a.Val2 is not null union all
select xx = a.Val3 where a.Val3 is not null union all
select xx = a.Val4 where a.Val4 is not null
) bb
)
from
MyTable a
More about these methods here:
MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906
You can also use UNPIVIOT for this. Sorry, don't have an example, but that's what Books Online is for.
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx
August 22, 2012 at 5:02 pm
Another example, using APPLY syntax:
DECLARE @T AS TABLE
(
pk integer PRIMARY KEY,
col1 integer NULL,
col2 integer NULL,
col3 integer NULL,
col4 integer NULL
);
INSERT @T
VALUES
(1, 4, 3, 2, 1),
(2, 5, NULL, 7, 8);
SELECT
MAX(f.x) AS Greatest
FROM @T AS t
CROSS APPLY
(VALUES (col1), (col2), (col3), (col4)) AS f (x)
GROUP BY t.pk
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 13, 2012 at 3:55 am
Another solution also using CROSS APPLY
SELECT MyTable1.x, MyTable2.y,..., G.Greatest
FROM MyTable1
JOIN MyTable2 ON MyTable1.key = MyTable2.key
.
.
CROSS APPLY (
SELECT MAX(T.v) AS Greatest
FROM ( VALUES (MyTable1.value), (MyTable2.value), ... ) AS T(v)
) AS G
Beer Molleman
December 16, 2012 at 6:13 pm
Neither of those last two CROSS APPLY examples will work in SS2K5 but this one will:
DECLARE @T AS TABLE
(
pk integer PRIMARY KEY,
col1 integer NULL,
col2 integer NULL,
col3 integer NULL,
col4 integer NULL
);
INSERT @T VALUES (1, 4, 3, 2, 1);
INSERT @T VALUES (2, 5, NULL, 7, 8);
SELECT Greatest=MAX(col), Least=MIN(col)
FROM @T
CROSS APPLY (
SELECT col1 UNION ALL SELECT col2 UNION ALL SELECT col3
UNION ALL SELECT col4) a(col)
GROUP BY pk
Admittedly though, I do prefer Paul's for SS2K8!
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
November 20, 2013 at 7:37 am
With Query1 As
( Select * from (values (1, 2), (4, 5), (2, Null), (2, 7), (1, 8)) As T(x,y))
select MAX(x) Greatest, Min(x) Least From Query1
Union All
Select Max(y), MIN(y) From Query1
June 5, 2014 at 8:36 am
rakesh code does'nt work. At all.
I think it works only with the only one example he tried it...
(10,100) => works (by chance!!!)
(100,10) => null, doestn't work (because he didn't do the other case)
(10, 4) => 10, doesn't work (because the test is wrong anyway.
July 24, 2015 at 6:42 pm
Hello All,
How would you do this with any and all dates?
I am trying to think of a way to create a Greatest Value that will handle dates, different date formats, Int, string, any thoughts?
Thanks In advance!
The pain of Discipline is far better than the pain of Regret!
July 25, 2015 at 1:08 am
SQLArnold (7/24/2015)
Hello All,How would you do this with any and all dates?
I am trying to think of a way to create a Greatest Value that will handle dates, different date formats, Int, string, any thoughts?
Thanks In advance!
The best way would be to store the dates with the correct datatype to begin with. After that, it's the same as the previous examples.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2017 at 5:59 pm
--For those on 2005, and/or wanting a function suited for variable number of columns, below is an alternative. [I've not tested this widely.]
CREATE FUNCTION dbo.fn_DelimitedSplitN4K (@pString NVARCHAR(4000), @pDelimiter NCHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS
RETURN
/*Used by fn_GreatestCSV.
Accepts a string & a specified delmiter. Returns that string as one-row-per-phrase, delimited as specified.
*/
/*Itzik-Style Cross-Join PseudoCursorCounter Method produces ZERO READS*/
WITH CTE_E1(n) AS --Alias for 10E1 scientific notation, its just ten SELECT 1's.
(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
), -- 1*10^1 [10 rows].
CTE_E2(n) AS (SELECT 1 FROM CTE_E1 a CROSS JOIN CTE_E1 b), -- 1*10^2 [100 rows[.
CTE_E4(n) AS (SELECT 1 FROM CTE_E2 a CROSS JOIN CTE_E2 b), -- 1*10^4 [10,000 rows].
CTE_tally(n) AS
(SELECT TOP (COALESCE(DATALENGTH(@pString)/2,0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --We can't order by a constant in ROW_NUMBER(). ROW_NUMBER() won't work without the ORDER BY clause. Use "(SELECT NULL)" to get around both requirements.
FROM CTE_E4
),
CTE_start AS
(SELECT 1 AS n1
UNION ALL
SELECT t.n + 1 AS n1
FROM CTE_tally t
WHERE SUBSTRING(@pString, t.n, 1) = @pDelimiter
),
CTE_len AS
(SELECT s.n1,
COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pString, s.n1), 0) - s.N1, 4000) AS l1
FROM CTE_start s
)
SELECT ROW_NUMBER() OVER(ORDER BY l.n1) AS seq,
SUBSTRING(@pString, l.n1, l.l1) AS phrase
FROM CTE_len l;
CREATE FUNCTION fn_GreatestCSV (@pString NVARCHAR(4000), @isOrdReturn bit) RETURNS dec(18,6)
/*
When @isOrdReturn equals False this function returns greatest numbers among supplied values in CSV; otherwise returns column position of that greatest value.
*/
BEGIN
DECLARE @mx AS dec(18,6) ;
DECLARE @ord AS dec(18,6)
SET @mx = (SELECT MAX(CAST(ds.phrase AS dec(18,6)))
FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
)
SET @Ord = (SELECT ds.seq
FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
WHERE CAST(ds.phrase AS dec(18,6)) = @mx
)
IF @isOrdReturn = 1 SET @mx = @Ord
RETURN @mx ;
END
--Example function calls:
SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 0) --Returns value of 8.030000.
SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 1) --Returns column # 2.
June 1, 2017 at 9:03 am
dmartin 38210 , I am a huge fan of Itzik. This is a great option, thanks!
I agree Jeff however, we do not have control how data sent to us is formatted, this is the problem. Each source, the State, has there way(ideas) of doing things, which are not always best practices.
We get date formats like
DDMMYYYY, YYYYMMDD, YYYMMDD, CCMMDD, DDMMYY
The pain of Discipline is far better than the pain of Regret!
June 1, 2017 at 9:51 am
dmartin 38210 - Wednesday, May 31, 2017 5:59 PM--For those on 2005, and/or wanting a function suited for variable number of columns, below is an alternative. [I've not tested this widely.]
CREATE FUNCTION dbo.fn_DelimitedSplitN4K (@pString NVARCHAR(4000), @pDelimiter NCHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS
RETURN
/*Used by fn_GreatestCSV.
Accepts a string & a specified delmiter. Returns that string as one-row-per-phrase, delimited as specified.
*//*Itzik-Style Cross-Join PseudoCursorCounter Method produces ZERO READS*/
WITH CTE_E1(n) AS --Alias for 10E1 scientific notation, its just ten SELECT 1's.
(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
), -- 1*10^1 [10 rows].
CTE_E2(n) AS (SELECT 1 FROM CTE_E1 a CROSS JOIN CTE_E1 b), -- 1*10^2 [100 rows[.
CTE_E4(n) AS (SELECT 1 FROM CTE_E2 a CROSS JOIN CTE_E2 b), -- 1*10^4 [10,000 rows].
CTE_tally(n) AS
(SELECT TOP (COALESCE(DATALENGTH(@pString)/2,0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --We can't order by a constant in ROW_NUMBER(). ROW_NUMBER() won't work without the ORDER BY clause. Use "(SELECT NULL)" to get around both requirements.
FROM CTE_E4
),
CTE_start AS
(SELECT 1 AS n1
UNION ALL
SELECT t.n + 1 AS n1
FROM CTE_tally t
WHERE SUBSTRING(@pString, t.n, 1) = @pDelimiter
),
CTE_len AS
(SELECT s.n1,
COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pString, s.n1), 0) - s.N1, 4000) AS l1
FROM CTE_start s
)
SELECT ROW_NUMBER() OVER(ORDER BY l.n1) AS seq,
SUBSTRING(@pString, l.n1, l.l1) AS phrase
FROM CTE_len l;CREATE FUNCTION fn_GreatestCSV (@pString NVARCHAR(4000), @isOrdReturn bit) RETURNS dec(18,6)
/*
When @isOrdReturn equals False this function returns greatest numbers among supplied values in CSV; otherwise returns column position of that greatest value.
*/
BEGIN
DECLARE @mx AS dec(18,6) ;
DECLARE @ord AS dec(18,6)SET @mx = (SELECT MAX(CAST(ds.phrase AS dec(18,6)))
FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
)
SET @Ord = (SELECT ds.seq
FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
WHERE CAST(ds.phrase AS dec(18,6)) = @mx
)IF @isOrdReturn = 1 SET @mx = @Ord
RETURN @mx ;
END--Example function calls:
SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 0) --Returns value of 8.030000.
SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 1) --Returns column # 2.
--probably more efficient to put something
-- likethis into an APPLY block:
SELECT TOP(1) seq, phrase
FROM dbo.fn_DelimitedSplitN4K(@pString, ',')
ORDER BY phrase DESC
[/code]
- casting as required for the sort.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply