CODEWITHSUNDEEP
mysqlsqlpdosql-order-by
Code With Desire
Code With Desire

Reputation: 173

mysql - IF...ELSE or CASE in ORDER BY statement

In my query below I am not getting the desired result. I want the results to sort according to the latest event occurrence based on two different date fields of two different tables. Let's see the query first.

SELECT R1.swp_to, R1.swp_type, R1.swp_date, M.mem_fname, M.mem_lname, M.mem_last_activity, DP.dp_photo, GREATEST(R1.swp_date, R2.swp_date) FROM swipes AS R1
LEFT JOIN swipes AS R2 ON(R1.swp_to = R2.swp_by AND R2.swp_to = R1.swp_by AND R2.swp_type <> 'left')
LEFT JOIN members AS M ON(R1.swp_to = M.mem_id)
LEFT JOIN display_photos AS DP ON(R1.swp_to = DP.dp_mem AND DP.dp_index = 1)
LEFT JOIN messages as MSG ON ((R1.swp_to = MSG.msg_from OR R1.swp_to = MSG.msg_to) AND (R1.swp_by = MSG.msg_from OR R1.swp_by = MSG.msg_to))
WHERE R1.swp_by = :mem AND R2.swp_by IS NOT NULL AND R1.swp_type <> 'left'
ORDER BY IF(MSG.msg_time IS NULL, 0, 1), R1.swp_date

Here in the ORDER BY statement we can see that there are two TIME fields msg_time and swp_date. Whenever there is a new match swp_date updates and when there is a new message msg_time updates. The records fetched using this query must be sorted as per the latest event occurrence (whichever date is the earliest of the two). My current ORDER BY statements does not fulfill the requirement. What am I missing here?

Upvotes: 1

Views: 72

Answers (1)

forpas
forpas

Reputation: 164099

You can use the function GREATEST():

ORDER BY GREATEST(COALESCE(MSG.msg_time, R1.swp_date), R1.swp_date) DESC

Upvotes: 1

Related Questions