How to use a conditional ORDER BY in MySQL? I have the table courses where we have some fields and also the field course(string), favorited(bool), favoritedDate(dateTime), dateUpload.

The teacher uploads the course and this date is stored in the dateUpload field. If the teacher flag this course as favorite this information is a number 1 in the field favorited and the date it was flagged is stored in the field favoritedDate.

Now I need to retrieve all courses from a specific teacher and then order the list by date: If the course is flagged favorited=1 should ordered by favoritedDate,dateUpload desc, if favorited=0 should be ordered only by  dateUpload desc.

This is what I have so far but it is not working:

SELECT * from courses where teacherId = 23 
ORDER BY 
  CASE favorited WHEN 1 THEN favoritedDate, dateUpload
    else dateUpload
  END
  DESC
;


Any idea how to solve this?
A case expression returns an atomic value so you can't return:

favoritedDate, dateUpload


I think this will do what you want:

ORDER BY
    CASE favorited WHEN 1 THEN favoritedDate 
                          ELSE dateUpload 
    END DESC, dateUpload