Mysql use DESC in case statement

mysql, php

Solution

Possibly bring back the sort field in the SELECT and then sort by that named field:-

  SELECT 
     SQL_CALC_FOUND_ROWS i.* , 
     c.title AS category_name, 
     u.name, 
     u.family, 
     i.thumb_image,
     CONCAT( u.name, ' ', u.family ) AS author,
     tumbnail_image_width,
     tumbnail_image_height,
     CASE (s.sortable)
        WHEN 1 THEN 100000000 - i.id 
        WHEN 2 THEN i.date_time
        WHEN 3 THEN i.order_display
     END  AS SortField
  FROM   contents i
  JOIN   categories c ON c.id = i.category
  JOIN   users u ON u.id = i.posted_by
  JOIN   settings s ON s.portal = i.portal
  WHERE 
         i.portal = '{$portal_id}'
         AND CASE WHEN post_type = 4
              THEN date(NOW()) BETWEEN i.from_dateTime AND i.to_dateTime 
         ELSE post_type = 1
         END
  AND i.t_status = 1
  ORDER BY SortField                  
  LIMIT {$portalSettings['display_post_count']};";

Note that you might have to cast the fields to a data type to do this.

Problem

in below sql command i want to use `DESC` after `WHEN 1 THEN i.id` line. i want to if `sortable` field is `1` then order by must be have like this command, ``` ORDER BY i.id DESC ``` Mysql : ``` SELECT SQL_CALC_FOUND_ROWS i.* , c.title AS category_name, u.name, u.family, i.thumb_image, CONCAT( u.name, ' ', u.family ) AS author, tumbnail_image_width, tumbnail_image_height FROM contents i JOIN categories c ON c.id = i.category JOIN users u ON u.id = i.posted_by JOIN settings s ON s.portal = i.portal WHERE i.portal = '{$portal_id}' AND CASE WHEN post_type = 4 THEN date(NOW()) BETWEEN i.from_dateTime AND i.to_dateTime ELSE post_type = 1 END AND i.t_status = 1 ORDER BY CASE (SELECT sortable FROM settings) WHEN 1 THEN i.id WHEN 2 THEN i.date_time WHEN 3 THEN i.order_display END LIMIT {$portalSettings['display_post_count']};"; ```

Original source