GREATEST - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GREATEST

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2012-08-13 : 09:41:51

hi!

i am trying to find a SQL Server equivalent of oracle's "GREATEST" function.

something that would return the largest (in this case timestamp) from a list of values given

example: greatest(timestamp1, temestamp2, timestamp3) it would return the most current date.

is there something like this in SQL Server i can use?

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-13 : 09:55:12

You'd have to write a user-defined function:

CREATE FUNCTION dbo.greatest(@v1 SQL_VARIANT, @v2 SQL_VARIANT=NULL, @v3 SQL_VARIANT=NULL) 
RETURNS SQL_VARIANT AS
BEGIN
	DECLARE @return SQL_VARIANT
	SELECT TOP 1 @return=VALUE 
	FROM (SELECT @v1 value UNION ALL SELECT @v2 UNION ALL SELECT @v3) a
	ORDER BY value DESC
	RETURN @return
END
GO

SELECT dbo.greatest(GETDATE(), GETDATE()-2, GETDATE()+3)

If you only have 1 or 2 parameters you would substitute DEFAULT or NULL for the ones that are missing. If you need more than 3 values you'd have to add additional parameters and UNION ALL to the code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 09:57:00

there's no corresponding function. but closest would be


SELECT MAX(val)
FROM (SELECT *
      FROM table p
      UNPIVOT (val FOR Cat IN (timestamp1, temestamp2, timestamp3))u
      )t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 10:09:27

If you are looking for max value of a few scalar variables/values ( and if you are on SQL 2008 or later)

DECLARE @x1 INT = 3, @x2 INT = 7, @x3 INT = 4;
SELECT MAX(c) FROM (VALUES (@x1),(@x2),(@x3)) t(c)

xrum
Yak Posting Veteran

87 Posts

Posted - 2012-08-13 : 10:32:21

thank you all, i got it!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-08-16 : 08:47:48

or use old CASE expression which is version independent


SELECT
	CASE 
		WHEN col1>col2 and col1>col THEN col1
		WHEN col2>col3 THEN col2
	ELSE
		col3
	END
FROM 
	TABLE

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-16 : 11:49:14

quote:
Originally posted by madhivanan

or use old CASE expression which is version independent

SELECT
CASE
WHEN col1>col2 and col1>col THEN col1
WHEN col2>col3 THEN col2
ELSE
col3
END
FROM
TABLE

Madhivanan

Failing to plan is Planning to fail

Not sure what version this fails on, but I think it works on 2008 and 2012:

SELECT
    ColA, ColB, ....
	,(
        SELECT MAX(MaxCol)
        FROM (VALUES (Col1), (Col2), (Col3)) AS MaxTable (MaxCol)
    ) AS MaxVal
FROM 
	TableName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 14:38:08

quote:
Originally posted by Lamprey

quote:
Originally posted by madhivanan

or use old CASE expression which is version independent

SELECT
CASE
WHEN col1>col2 and col1>col THEN col1
WHEN col2>col3 THEN col2
ELSE
col3
END
FROM
TABLE

Madhivanan

Failing to plan is Planning to fail
Not sure what version this fails on, but I think it works on 2008 and 2012:
SELECT
ColA, ColB, ....
,(
SELECT MAX(MaxCol)
FROM (VALUES (Col1), (Col2), (Col3)) AS MaxTable (MaxCol)
) AS MaxVal
FROM
TableName

any version before 2008 it will fail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-08-16 : 17:57:46

MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources