I have two tables in mysql db with same schema but different data and I want to select the record that is the latest based on date field on both the tables.

Table : FamilyStatement:

Table : AccountStatement:

when I execute the following query by doing a union on both the tables and MAX(dateEmailed) I get the correct value returned in the dateemailed column but the query does not brings all the other related fields from that table.

SELECT familyid,MAX(dateemailed),amount,statementurl FROM 
(
   SELECT familyid,dateemailed,amount,statementurl  FROM familystatement
   UNION
   SELECT familyid, dateemailed, amount,statementurl FROM accountstatement) a 
GROUP BY familyid ORDER BY familyid

result

expected

Could you please let me know how I can achieve this result.

CodePudding user response：

Looks like you need a WHERE clause which will fetch row(s) for the MAX date value. Something like this:

select *
from (select familyid, dateemailed, amount, statementurl from familystatement
      union
      select familyid, dateemailed, amount, statementurl from accountstatement)
     )
where datemailed = (select max(datemailed)
                    from (select datemailed from familystatement
                          union
                          select datemailed from accountstatement
                         )
                   );
                   

Could be simplified if you used a CTE:

with temp as
  (select familyid, dateemailed, amount, statementurl from familystatement
   union
   select familyid, dateemailed, amount, statementurl from accountstatement
  )
select *
from temp
where datemailed = (select max(datemailed) from temp);

Code you wrote does return the MAX date value from the union, but GROUP BY clause on non-aggregated columns will return wrong result (as you already noticed).