开发者

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!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜