mySQL query -- sort by date
I am merging two tables, each one with different events data. Each table has a date for the events.
How do I combine these events to sort them seamlessly by date?
SELECT
t1.eventName,
t1.eventTime,
t2.evntName,
t2.evntTime
FROM t1
LEFT JOIN t2 ON (t1.eventID = 开发者_运维问答t2.eventID)
ORDER BY ??? DESC
LIMIT 10
@Gonzalo is close but not quite there. You need to put each table into subqueries and alias each column you want in the final result. If one table doesn't have a column that you need from another use a literal and alias it.
(SELECT eventName, eventTime, foo FROM t1)
UNION ALL
(SELECT evntName AS eventName, evntTime AS eventTime, NULL AS foo FROM t2)
ORDER BY eventTime DESC
Put any WHERE
clauses inside the subqueries.
Not 100% sure what you are trying to do here, but maybe this:
ORDER BY GREATEST(t1.eventTime, t2.eventTime) DESC
ETA:
Looks like you can't compare dates directly with GREATEST, so you might need to do this:
ORDER BY GREATEST(UNIX_TIMESTAMP(t1.eventTime), UNIX_TIMESTAMP(t2.eventTime)) DESC
You're joining the tables, wich will result in records of both tables combined in one row. What you have to do if you really want to merge tables like that, is an UNION query. Something like this:
SELECT eventName, eventTime FROM (
SELECT eventName, eventTime
FROM t1
UNION
SELECT eventName, eventTime
FROM t2
)
ORDER BY eventTime
So, you merge the results of both tables in a subquery, and the you sort it.
Good luck!
精彩评论