[RESOLVED] Selecting Distinct entries

[Neko_D]

So I am running into a bit of a problem. I have 4 tables, 3 of my tables reference the ids for the one remaining table. The three tables update their time stamps on updates. So I am trying to come up with a query that will return the 5 most recently updated master table rows, based on the most recent entry for the id in any one of the 3 other tables, ordered by date.

Thanks for any ideas.

[laserlight]

Something along these lines might work:

SELECT id, date FROM (
    SELECT Master.id, date FROM Master, Table1 WHERE Master.id=Table1.master_id
    UNION
    SELECT Master.id, date FROM Master, Table2 WHERE Master.id=Table2.master_id
) ORDER BY date DESC LIMIT 5;

[Neko_D]

Thanks LaserLight, that gets me one step closer. Now the 3 other tables have a Many to One relationship with the master table. So I want to pull out the 5 most recent distinct master rows. The queries you provided get me every row in all of those tables.

[laserlight]

You could try something along the lines of:

SELECT id, MAX(date) FROM (
    SELECT Master.id, date FROM Master, Table1 WHERE Master.id=Table1.master_id
    UNION
    SELECT Master.id, date FROM Master, Table2 WHERE Master.id=Table2.master_id
) GROUP BY id ORDER BY date DESC LIMIT 5;

[Neko_D]

Is there no way to write a query that puts distinct on just one column and not on the others?

[laserlight]

What do you mean?

[tomhath]

The first query laserlight posted will return the five most recent updates to the master table.

The second will return the five most recently updated ids in the master table.

[Neko_D]

Ah ok, sorry I do not really have adequte test data, so I made some thanks for the help laserlight.