Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


GREATEST and LEAST function


GREATEST and LEAST function

Author
Message
corallo.d
corallo.d
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14383 Visits: 9729
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
corallo.d
corallo.d
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
rakesh1084
rakesh1084
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 10
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 


Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3260 Visits: 11771
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
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10340 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Beer Molleman
Beer Molleman
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 29
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
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4275 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
hs_abed
hs_abed
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
John Doe-372015
John Doe-372015
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search