2

I have the following schema:

table_1
id, updated_at

table_2
id, updated_at

table_3
id, updated_at

and in total there are about 12 tables that I want to get the updated_at column and determine the MAX() aggregate value from all of them, based on a single id value.

How would I set up this query?

  • Same id across several tables? Ugh! – Rick James May 1 '18 at 4:11
2

Get the maximum updated_at for a given id from each table, combine the results into one row set and then get the maximum from it

SELECT
  MAX(updated_at)
FROM
  (
    SELECT MAX(updated_at) AS updated_at FROM table1 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table2 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table3 WHERE id = @param_id
    UNION ALL
    ...
  ) AS derived
;

This is much more efficient then combining the tables first and then filtering and aggregating the combined set, even though the code would look simpler that way.

This method can also benefit from an index on (id, udpated_at) in each table, while probably no index would help if you decided to combine whole tables first.

0

First select and union all table data and then use max() with group by id

select max(t.updated_at) from(
  select id,updated_at from table1
  union
  select id,updated_at from table2
  union
  select id,updated_at from table3
  union
  select id,updated_at from table4
) as t group by t.id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.