CODEWITHSUNDEEP
mysqlsqldatabaseunion
Braydon Batungbacal
Braydon Batungbacal

Reputation: 1038

How do I order this UNION MySQL Query?

I have the following query and want to order the results by the CREATED column, how would I go about doing it?

(SELECT media.id, media.accessKey, media.internalName, media.type, media.modified, users.username, users.id, media.created,
0 AS reposted
FROM media
LEFT JOIN users ON users.id = media.userId)
UNION
(SELECT media.id, media.accessKey, media.internalName, media.type, media.modified, users.username, reposts.userId, reposts.created,
1 AS reposted
FROM reposts
LEFT JOIN media ON media.id = reposts.mediaId
LEFT JOIN users ON users.id = reposts.userId)

Upvotes: 0

Views: 57

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21677

You just need to add the order by clause to your query (the use of ( around the queries is not mandatory):

SELECT media.id,
       media.accessKey,
       media.internalName,
       media.type,
       media.modified,
       users.username,
       users.id,
       media.created,
       0 AS reposted
FROM media
LEFT JOIN users ON users.id = media.userId
UNION
SELECT media.id,
       media.accessKey,
       media.internalName,
       media.type,
       media.modified,
       users.username,
       reposts.userId,
       reposts.created,
       1 AS reposted
FROM reposts
LEFT JOIN media ON media.id = reposts.mediaId
LEFT JOIN users ON users.id = reposts.userId
ORDER BY CREATED ASC

You can check this sqlfiddle to see it working.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271141

Add:

order by created

At the end of your query.

Upvotes: 1

Related Questions