CODEWITHSUNDEEP
mysqlsqlunion
santa
santa

Reputation: 12512

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

Upvotes: 1

Views: 8989

Answers (3)

Walf
Walf

Reputation: 9308

@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.

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60498

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

Upvotes: 1

Gonzalo Larralde
Gonzalo Larralde

Reputation: 3541

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!

Upvotes: 0

Related Questions