Hi, I'm having some trouble figuring out how to do this, it seems like it should be easier than I'm making it. I have a bunch of tables that are identical in structure and all have a field called post_date (formatted 2010-05-24 15:42:25) which is the date the row was entered and an auto-incremented ID, but each table has a different numbers of entries. I want to be able to select the the newest 10 entries from all the tables by post_date. So that could mean the results are returned as a row from table 1, then from table 4, then from table 1 again, then table 8, etc etc because it's based on post date. Any idea how to go about this?! Thanks!
Code: Select * from ( select * from table1 order by post_date limit 10 union select * from table2 order by post_date limit 10 union select * from table3 order by post_date limit 10 union select * from table4 order by post_date limit 10 ) order by post_date limit 10 Something like that?
Thank you! Not sure why I was making it so complicated. I ended up using this because I was getting some mysql errors: Code: SELECT * FROM ( (SELECT * FROM 14_posts ORDER BY ID DESC LIMIT 10) UNION (SELECT * FROM 15_posts ORDER BY ID DESC LIMIT 10) UNION (SELECT * FROM 16_posts ORDER BY ID DESC LIMIT 10) UNION (SELECT * FROM 17_posts ORDER BY ID DESC LIMIT 10) UNION (SELECT * FROM 18_posts ORDER BY ID DESC LIMIT 10 ) ) AS Tmp ORDER BY post_date DESC LIMIT 10