Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

GREATEST and LEAST function Expand / Collapse
Author
Message
Posted Thursday, April 24, 2008 6:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 10, 2008 4:42 AM
Points: 2, Visits: 10
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.
Post #489858
Posted Thursday, April 24, 2008 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #489865
Posted Thursday, April 24, 2008 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 10, 2008 4:42 AM
Points: 2, Visits: 10
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.
Post #489949
Posted Wednesday, August 22, 2012 1:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 6, 2014 5:43 AM
Points: 5, Visits: 7
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 

Post #1348690
Posted Wednesday, August 22, 2012 3:34 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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



Post #1348778
Posted Wednesday, August 22, 2012 5:02 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1348803
Posted Thursday, December 13, 2012 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:24 AM
Points: 1, Visits: 28
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
Post #1396097
Posted Sunday, December 16, 2012 6:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1397047
Posted Wednesday, November 20, 2013 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:21 AM
Points: 7, Visits: 73
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
Post #1516066
Posted Thursday, June 5, 2014 8:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:32 AM
Points: 1, Visits: 2
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.

Post #1577836
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse