mysql 选最大的元素,如何在MySQL中选择每行的两个元素的最大值

I have got a table that is a result of a (My)SQL query. In this table I have the post creation timestamp and the user comment creation timestamp. The trick is that not all posts have a comment (so some comment_creation are NULL).

I would like to order the rows according of the most recent creation time of the post or user comment.

How can I get the max(post_creation, comment_creation) of each row and order them (DESC order)?

Thanks for all contribution.

解决方案

Based on your previous question, try:

SELECT p.id AS post_id,

p.author_id AS post_author_id,

p.created_date AS post_created,

c.author_id AS comment_author_id,

c.created_date AS comment_created,

p.title,

c.content,

coalesce(c.created_date,p.created_date) AS sort_date

FROM Posts p

LEFT JOIN Comments c ON p.id = c.post_id

WHERE p.author_id = $userId

UNION ALL

SELECT p.id AS post_id,

p.author_id AS post_author_id,

p.created_date AS post_created,

c.author_id AS comment_author_id,

c.created_date AS comment_created,

p.title,

c.content,

c.created_date AS sort_date

FROM Posts p

RIGHT JOIN Comments c ON p.id = c.post_id

WHERE c.author_id = $userId

ORDER BY sort_date