CODEWITHSUNDEEP
mysqlsql
Dan
Dan

Reputation: 1110

Order all union results by timestamp

The query is

SELECT L.timestamp AS timestamp . . . 
FROM like AS L 
INNER JOIN users AS U 
ON U.id = L.user_id 
INNER JOIN posts AS P 
ON P.user_id = :userid 
WHERE L.post_id = P.id 
  UNION 
SELECT P.timestamp . . . 
FROM post AS P 
INNER JOIN users AS U 
ON U.id = P.user_id 
WHERE FIND_IN_SET(:userid , P.users) 
  UNION 
SELECT C.timestamp AS timestamp . . . 
FROM comment AS C 
INNER JOIN posts AS P 
ON P.user_id = :userid 
INNER JOIN users AS U 
ON U.id = C.user_id 
WHERE C.post_id = P.id 
ORDER BY timestamp DESC

I tried adding the ORDER BY timestamp DESC at the end but it still isn't showing the latest one first. It's showing one from; 10 days ago, 9 days ago, 1 month ago, and 4 days ago, in that order.

Upvotes: 2

Views: 446

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You do not need to put the entire query in a from clause. You can just put parentheses around each subquery. This is clearly stated in the documentation:

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one.

So:

(SELECT L.timestamp AS timestamp . . . 
 FROM like L JOIN
      users U 
      ON U.id = L.user_id JOIN
      posts P 
      ON P.user_id = :userid 
 WHERE L.post_id = P.id 
) UNION
(SELECT P.timestamp . . . 
 FROM post P JOIN
      users U 
      ON U.id = P.user_id 
 WHERE FIND_IN_SET(:userid , P.users) 
) UNION 
(SELECT C.timestamp AS timestamp . . . 
 FROM comment C JOIN
      posts P 
      ON P.user_id = :userid JOIN
      users U 
      ON U.id = C.user_id 
 WHERE C.post_id = P.id 
)
ORDER BY timestamp DESC;

Note: If your subqueries are not returning duplicate rows, then change the UNION to UNION ALL. There is no need to incur the overhead of removing duplicates.

Upvotes: 2

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You need to use the query as sub-query to do an ORDER BY on the whole result:

SELECT * FROM (
    SELECT L.timestamp AS timestamp FROM like AS L 
        INNER JOIN users AS U ON U.id = L.user_id 
        INNER JOIN posts AS P ON P.user_id = :userid 
    WHERE L.post_id = P.id 
    UNION
    SELECT P.timestamp . . . FROM post AS P 
        INNER JOIN users AS U ON U.id = P.user_id 
    WHERE FIND_IN_SET(:userid , P.users) 
    UNION 
    SELECT C.timestamp AS timestamp . . . 
    FROM comment AS C 
        INNER JOIN posts AS P ON P.user_id = :userid 
        INNER JOIN users AS U ON U.id = C.user_id 
    WHERE C.post_id = P.id 
)x ORDER BY x.timestamp DESC

Upvotes: 2

Related Questions