Questions › Selecting row with max date from multiple almost identical tables
tenub Asked

If I can retrieve the most recent name for each id from a table in a MySQL database like so:

SELECT n.id, n.name, n.date
    FROM $table AS n
INNER JOIN
(SELECT id, MAX(date) AS date
    FROM $table GROUP BY id)
AS max
USING (id, date);

How could I retrieve the most recent name from three almost identical tables (call them $table, $table2, $table3)? They all share the same column structure and the id found from one table may or may not be present in the other two. Think of it as one large table split into three (but with two of them containing two extra columns that are irrelevant in this instance). Would UNION be the best solution? If so, is there a way to do it without a mile-long query?


Constraint:

id is not an auto-incrementing unique integer unfortunately

Comments :
davejal replied

Did you see@Gordon linoff 's answer? Did it work?

tenub replied

it didn't. names contain just about every character since they're player names from a game.

davejal replied

It would be helpful if you added sample data and structure


3 Answers :
Gordon Linoff answered

You can use union all. One slight simplification is the group_concat()/substring_index() trick:

select id, max(date) as date,
       substring_index(group_concat(name order by date desc), ',', '') as MostRecentName
from (select t.* from $table1 t union all
      select t.* from $table2 t union all
      select t.* from $table3
     ) t
group by id;

This does make certain assumptions. The name cannot contain , (although it is easy enough to change the separator. In addition, the intermediate result for the group_concat() cannot exceed a certain threshold (which is determined by a user-settable system parameter).

davejal answered

You could try:

SELECT n.id, n.name, n.date
FROM table1 where id in (select max(id) from table1)
union
SELECT n.id, n.name, n.date
FROM table2 where id in (select max(id) from table2)
union
SELECT n.id, n.name, n.date
FROM table3 where id in (select max(id) from table3)

Every inner query selects the highest id from the table and then searches for the corresponding fields in the outer query.

tenub replied
id is not an auto-incrementing unique integer unfortunately.;
davejal replied
no problem, please edit your question to explain this also;
tenub answered

This ended up being the only solution I could think of:

SELECT n.id, n.name, n.date FROM (
        SELECT id, name, date FROM $table
            UNION ALL
        SELECT id, name, date FROM $table2
            UNION ALL
        SELECT id, name, date FROM $table3
    ) AS n INNER JOIN (
    SELECT id, MAX(date) AS date FROM (
        SELECT id, date FROM $table
            UNION ALL
        SELECT id, date FROM $table2
            UNION ALL
        SELECT id, date FROM $table3
    ) AS t
    GROUP BY id
) AS max USING (id, date)