SQL getting max date from two tables

max, oracle, sql

Solution

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.

Problem

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?

Original source