I am combining tables to get all their latest entries. The first query below works fine. When I add a third table to it the results get weird.
This one works:
(SELECT AL.alID, AL.al_date AS ts FROM AL)
UNION
(SELECT MRA.mraNR, MRA.mra_date FROM MRA)
ORDER BY ts DESC LIMIT 20
And gives result:
AL.alID | ts
14864 | 2014-08-01 23:43:08
14865 | 2014-08-01 23:36:46
2401 | 2014-08-01 18:07:06
2401 | 2014-08-01 18:06:00
While, this query:
(SELECT AL.alID, AL.al_date AS ts FROM AL)
UNION
(SELECT MRA.mraNR, MRA.add_date FROM MRA)
UNION
(SELECT AMG.mraNR, AMG.lastupd FROM AMG GROUP BY AMG.mraNR)
ORDER BY ts DESC LIMIT 20
...gives this result, where the first nr doesn't make sense at all:
3134383634 | 2014-08-01 23:46:20
3134383634 | 2014-08-01 23:43:08
3134383635 | 2014-08-01 23:38:56
3134383635 | 2014-08-01 23:36:46
32343031 | 2014-08-01 18:07:06
My questions:
- how can I solve this in the query?
- how can I group the results so it won't show the doubles (AL.alID)