MAX over two fields? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

MAX over two fields?

Author

Topic

vermorel
Starting Member

26 Posts

Posted - 2007-11-05 : 02:51:11

I have (roughly) the following query

SELECT MAX(LastWritten) AS [LastUpdated]
FROM MyTable

Yet, I would like to be able to write

SELECT MAX(LastWritten, LastRead) AS [LastUpdated)
From MyTable

Note: the actual query is more complex with a GROUP BY clause.

Does anyone knows how to do that?

Thanks in advance,
Best regards,

Joannes
http://www.lokad.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 02:53:41

SELECT TOP 1 LastWritten, LastRead
FROM LastUpdated
ORDER BY LastUpdated

Without some actual DDL (or present query) this is the closest I can come up with.

E 12°55'05.25"
N 56°04'39.16"

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-05 : 03:16:29

MAX(LastWritten, LastRead) ->
case when LastWritten>LastRead then LastWritten else LastRead end

I expect that both values are never NULL
If they are, add some isnulls

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-05 : 03:23:52

--This method gives you much more scalability. You can thank Peso for the code, he helped me out with this same question a while back

select *, ( SELECT MAX(p)
FROM
( SELECT a.LastWritten AS p
UNION ALL
SELECT a.lastRead) AS d
)
from
mytable a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources