SQL getting max date from two tables

I have two tables

USER (one row per user)

id,username,firstname,lastname,lastmodified
1,johns, John,Smith, 2009-03-01
2,andrews, Andrew,Stiller, 2009-03-03


STUDIES (multiple rows per user)

id,username,lastmodified
1,j开发者_高级运维ohns, 2009-01-01
1,johns, 2009-02-01
1,johns, 2009-07-01
2,andrews,2009-05-05
2,andrews,2009-04-04

I want to get users details and the NEWEST date from the two tables:

johns,John,Smith,2009-07-01
andrews,Andrew,Stiller,2009-05-05

Help?

---

You need combination of MAX and GREATEST functions here:

select u.username
     , u.firstname
     , u.lastname
     , greatest(u.lastmodified,max(s.lastmodified))
  from USER u
     , STUDIES s
 where s.id = u.id
 group by u.id
     , u.username
     , u.firstname
     , u.lastname
     , u.lastmodified

MAX -- for aggregation, GREATEST -- for maximum of two values.

---

For those who need a simple straightforward select:

select max(lastmodified) 
from (
    select max(lastmodified) as lastmodified from USER 
    union 
    select max(lastmodified) as lastmodified from STUDIES
);

This will get the max date from USER then the max date from STUDIES, and then it will return the max of those 2. Also you may want to add where clause and some conditions to the inner selections in order to improve the result.

---

Something like this

select username, max(lastmodified) from (
   select username, lastmodified from user 

   union all

   select username, max(lastmodified) as lastmodified 
   from studies
   group by username
) s
group by username

---

SELECT MAX(Date) FROM Users u FULL JOIN Studies s ON u.Username=s.Username GROUP BY Username

---

SELECT MAX(updatedDate) as latestUpdated FROM ( SELECT updatedDate FROM audio UNION ALL SELECT updatedDate FROM videos )foo