MySQL Compare timestamps from two different tables and sort the latest record-CodePudding

I have the following query to get data from the tables. I want to sort the data based on timestamp from two different tables. Whichever timestamp is the latest one should be sorted as first record followed by the second and third etc. However, I see that this is partially working. It is sorting latest record but based on only one table messages. If a record is newer record comes in the swipes table then that one should come first which is not happening.

SELECT usr.id, usr.gender, usr.last_activity, CONCAT(usr.first_name, ' ', usr.last_name) as fullName FROM
(SELECT swp_to uid, created_at cat FROM swipes WHERE swp_from = :user AND first_swp IN ('like','superlike') AND second_swp IN ('like','superlike')
  UNION
  SELECT swp_from uid, created_at cat FROM swipes WHERE swp_to = :user AND first_swp IN ('like','superlike') AND second_swp IN ('like','superlike')
) u
LEFT JOIN messages m ON u.uid IN (m.msg_from, m.msg_to)
INNER JOIN users usr ON u.uid = usr.id
GROUP BY u.uid ORDER BY GREATEST(MAX(m.created_at), MAX(u.cat)) DESC LIMIT $start, $limit

I guess that the GREATEST() function is not working as expected. It is only sorting based on the latest messages which is about MAX(m.created_at) only. It feels like as if it is ignoring that MAX(u.cat) is also there to compare with. What mistake am I making here?

UPDATE

SCREENSHOT FOR QUERY

CodePudding user response：

When any of arguments provided into GREATEST() function is NULL then the function returns NULL despite of another arguments values.

ORDER BY treates NULL value as the most least one.

You must provide correct greatest value selection which takes into account that a value can be NULL. For example, you may use

ORDER BY GREATEST(COALESCE(MAX(m.created_at), MAX(u.cat)), 
                  COALESCE(MAX(u.cat), MAX(m.created_at))) DESC

In this case the expression will return NULL only when both values are NULL.