I have 8 tables. I would like to retrieve the latest (newest) row from each table. I’d like to achieve this in a single, efficient query considering that each of the 8 tables will run into the millions of records each. In pseudo-code:
$sql = "(SELECT * FROM level1)
UNION
(SELECT * FROM level2)
UNION
(SELECT * FROM level3)
UNION
(SELECT * FROM level4)
UNION
(SELECT * FROM level5)
UNION
(SELECT * FROM level6)
UNION
(SELECT * FROM level7)
UNION
(SELECT * FROM level8)
ORDER BY id DESC LIMIT 8;";
(SELECT
1 AS sort_col
, t1.*
FROM t1
ORDER BY datecolumn DESC
LIMIT 1)
UNION
(SELECT
2 AS sort_col
, t2.*
FROM t2
ORDER BY datecolumn DESC
LIMIT 1)
UNION
...
UNION
(SELECT
8 AS sort_col
, t8.*
FROM t8
ORDER BY datecolumn DESC
LIMIT 1)
ORDER BY sort_col
Thanks a lot for the responses! Just wanted to add that I’d figured out how to further tweak the query. I’ll have to look at that nice example above closely!! Anyway, here’s the tweak, which does this: pulls a random record from each of the 8 tables (and seems to) ensure a single record is pulled from each. What say you guys about this one?
(SELECT * FROM level1 LIMIT 1) UNION (SELECT * FROM level2 LIMIT 1) UNION (SELECT * FROM level3 LIMIT 1) UNION (SELECT * FROM level4 LIMIT 1) UNION (SELECT * FROM level5 LIMIT 1) UNION (SELECT * FROM level6 LIMIT 1) UNION (SELECT * FROM level7 LIMIT 1) UNION (SELECT * FROM level8 LIMIT 1) ORDER BY RAND() LIMIT 8;
No it doesn’t. It pulls the first record from each table.
If you want a random record from each table, put the ORDER BY RAND in each single SELECT.
(and seems to) ensure a single record is pulled from each.
Yes it does.
What say you guys about this one?
(SELECT * FROM level1 LIMIT 1) UNION (SELECT * FROM level2 LIMIT 1) UNION (SELECT * FROM level3 LIMIT 1) UNION (SELECT * FROM level4 LIMIT 1) UNION (SELECT * FROM level5 LIMIT 1) UNION (SELECT * FROM level6 LIMIT 1) UNION (SELECT * FROM level7 LIMIT 1) UNION (SELECT * FROM level8 LIMIT 1) ORDER BY RAND() LIMIT 8;
You can get rid of that LIMIT 8, because the result of all the unions is always 8 lines at the most.
This isn’t at all what you were asking for in your OP though
guido, thanks. You misunderstand my last posts. In fact, your example query has led to a visually noticeable performance increase in retrieving the needed records – THANK YOU!
Yes, your first query does pull the latest record from each table; I agree. That clarified, I was referring to the next query I posted on this thread (the post at 22:53pm, which was a variation on your original query).
You’re also right in that this was not the original question… but as it was something very similar that I ALSO needed to achieve, I thought I’d post the other query as well, so that both queries could be shown – or shot down – here. It’s always good to find relatively like things together.
Anyway, here’s the final 2 queries that I’m using, and if you can see a better way still, I’m happy to hear your advice! Did my best to format the queries, sorry they’re not perfect.
// Get latest record from each of X tables in a single query.
(SELECT
1 AS difficulty,
level1.*
FROM level1
ORDER BY id DESC
LIMIT 1)
UNION
(SELECT
2 AS difficulty,
level2.*
FROM level2
ORDER BY id DESC
LIMIT 1)
UNION
...etc...
(SELECT
8 AS difficulty,
level8.*
FROM level8
ORDER BY id DESC
LIMIT 1)
ORDER BY difficulty;
// Get a single random record from each of X tables in a single query.
(SELECT
1 AS sort_col,
t1.*
FROM t1
ORDER BY RAND()
LIMIT 1)
UNION
(SELECT
2 AS sort_col,
t2.*
FROM t2
ORDER BY RAND()
LIMIT 1)
UNION
...etc...
(SELECT
8 AS sort_col,
t8.*
FROM t8
ORDER BY RAND()
LIMIT 1)
ORDER BY sort_col;
r937, I detect a bit of sarcasm in your post… usually, that’s not all that nice, but I know you mean well. Just wanted to say thanks for jumping in and giving me a laugh with the “this is efficient ???” comment… I appreciate the great deal of help you’ve given me here, thank you.