SQL: find newest date from three columns

TyrantCow

i generally just use pretty basic SQL, for the life of me i can't figure out how to approach this...

using SQL I need to find the newest date from three different date fields. i was thinking case statements; but, maybe i'm just using them wrong?

SELECT PAT.Update_Date, PADD.Update_Date, PELG.Update_Date, 

CASE WHEN
(CASE WHEN PAT.Update_Date >= PADD.Update_Date THEN PAT.Update_Date ELSE PADD.Update_Date END AS PATvsPADD) >=
(CASE WHEN PAT.Update_Date >= PELG.Update_Date THEN PAT.Update_Date ELSE PELG.Update_Date END AS PATvsPELG) 
THEN PATvsPADD ELSE PATvsPELG END AS WhoWins

FROM PAT, PADD, PELG
WHERE PAT.Id = PADD.Id AND PADD.Id = PELG.Id

obviously, this isn't working out for me, or i wouldn't be here. but, am i even headed in the right direction?

after i get the select business down, i need to modify the where so i can check against a date, i.e. have any of these dates been updated in the past two weeks?

bowen

You can certainly do that. But it gets really messy really fast with values greater than 3. Are you using SQLServer or MySQL?

TyrantCow

of course right after i post this i find what i need...

CASE
     WHEN PAT.Update_Date >= PADD.Update_Date AND PAT.Update_Date >= PELG.Update_Date THEN PAT.Update_Date
     WHEN PADD.Update_Date >= PAT.Update_Date AND PADD.Update_Date >= PELG.Update_Date THEN PADD.Update_Date
     WHEN PELG.Update_Date >= PAT.Update_Date AND PELG.Update_Date >= PADD.Update_Date THEN PELG.Update_Date
     ELSE PAT.Update_Date
END AS WhoWins

now to get the where clause to work off of that, gonna just try a nested select with this code.

TyrantCow

bowen wrote: »

You can certainly do that. But it gets really messy really fast with values greater than 3. Are you using SQLServer or MySQL?

Oracle

bowen

TyrantCow wrote: »

bowen wrote: »

You can certainly do that. But it gets really messy really fast with values greater than 3. Are you using SQLServer or MySQL?

Oracle

http://www.techonthenet.com/oracle/functions/greatest.php

MySQL has this too. SQLServer is the odd man out.

SELECT greatest(col1,col2,col3,...) AS WhoWins FROM someTable

I think that's kosher in Oracle.

TyrantCow

bowen wrote: »

TyrantCow wrote: »

bowen wrote: »

You can certainly do that. But it gets really messy really fast with values greater than 3. Are you using SQLServer or MySQL?

Oracle

http://www.techonthenet.com/oracle/functions/greatest.php

MySQL has this too. SQLServer is the odd man out.

oh my, so much cleaner

thanks!

bowen

:^: wayyyy better for future additions too.

bowen

Also, apparently unpivot works too, and is available in SQL Server.