mysql - Problem with ORDER BY CASE WHEN, wrong rows position

Question

I have a SQL query as below:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 
LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID 
WHERE 1=1 
AND wp_posts.post_type = 'seminar' 
AND wp_posts.post_status = 'publish' 
AND wp_postmeta.meta_key = 'end' 
ORDER BY CASE WHEN wp_postmeta.meta_value+0 > 20210126 THEN wp_postmeta.meta_value+0 END ASC, wp_postmeta.meta_value+0 DESC 
LIMIT 0, 10

I want to show rows have meta_value column's value larger than 20210126 on top, but they appeared after default order condition (wp_postmeta.meta_value+0 DESC).

How can i switch them with this query?

question from:https://stackoverflow.com/questions/65903899/problem-with-order-by-case-when-wrong-rows-position

Answer 1

Use a boolean for ordering to put them first explicitly. Then you can order each of the groups separately:

ORDER BY (wp_postmeta.meta_value+0 > 20210126) DESC,  -- put the bigger ones first
         (CASE WHEN wp_postmeta.meta_value+0 > 20210126 THEN wp_postmeta.meta_value+0 END) ASC,
         wp_postmeta.meta_value+0 DESC