Ordering & Limiting multiple table rows by date

[masteroleary]

My goal is to select rows from two tables 'shoot' & 'video' and have mysql order those tables by date and limit the number of rows returned. I want to list the latest 10 rows but am uncertain how many rows from each table will be the latest. I may have more recent shoots than videos or the opposite. How can I use a single ORDER BY and LIMIT statement for both selections?

 

This is the code I used last which may or may not be anywhere near the direction I need to go.

SELECT id, name, description, directory, date
FROM shoot
LIMIT 3 
UNION (

SELECT id, name, description, filename, date
FROM video
)
ORDER BY date
LIMIT 0 , 30

[masteroleary]

Havent gotten a reply. Last time that happened I tried to do something mysql couldnt. Is this the case now?

[per1os]

SELECT id, name, description, directory, `date`
FROM shoot
LIMIT 3 
UNION (
SELECT id, name, description, filename, `date`
FROM video
)
ORDER BY `date`
LIMIT 0,30

 

The limit you should not have any spaces. If you are using MySQL make sure you are using a version that supports subquerys. The date column can be considered a "special" word, using the backticks (`) to enclose it may also help.

 

If that does not work do OR DIE(mysql_error()) and report back the error or show some code.

[masteroleary]

Thanks