SQL getting max date from two tables

Question

Hi,

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,johns, 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?

Answer 1

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.

Answer 2

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

Answer 3

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