MySQL Conditional Order By

MySql conditional order by

This will do the trick..

 SELECT * 
    FROM my_table 
    WHERE 1  
    ORDER BY 
      CASE price WHEN 0 THEN 1
       ELSE -1
      END ASC, price asc, id asc

SQL conditional order by?

You can use two keys in the order by:

order by (t.completed = '0000-00-00 00:00:00') desc,  -- put these first
         t.completed desc

MySQL conditional ORDER BY ASC/DESC for date column

Try this:

ORDER BY post_status ASC,
CASE post_status WHEN 'future' THEN POST_DATE END ASC,
CASE WHEN post_status <> 'future' THEN post_date END DESC

Conditional sorting in MySQL

You can do this with a single query by putting all the logic in the order by clause:

select *
from conferences
where ev_end >= '2014-06-30' 
order by (notification >= '2014-06-30') desc,
         (case when notification >= '2014-06-30' then notification end) asc,
         (case when notification < '2014-06-30' then ev_start end) asc;

Note that union explicitly does not guarantee the order of the result set. If you want the result set in a particular order, then you want an order by in the outermost part of the query.

Conditional sorting in MySQL?

You could try ORDER BY (done asc, aux desc) where aux is computed with a CASE to yield either priority or date based on the value of done (you may have to cast them to the same type to fit in the same expression, e.g. cast the date to a suitable integer day number).

For example:

SELECT * FROM tab
ORDER BY done desc,
         case done
             when 0 then prio 
             else to_days(thedate)
         end desc;

Conditional ORDER BY

The case statement is super-useful and lets you do all sorts of fun things. Works everywhere a single column works.

SELECT
    id,
    item,
    action,
    t1.`desc`,
    time
FROM t1
WHERE bus = 6
ORDER BY 
  case t1.`desc` when '' then action else '' end DESC, 
  time ASC

Conditional Order by multiple columns Mysql

I tried using CASE but it didn't work as per my liking, got a simpler solution to work.

Select * from mytable order by Condition1 Desc, condition2 desc, condition3 desc

Conditional Order By on multiple columns

Try this:

SELECT year, month, btype, bnumber
FROM some_table
ORDER BY year ASC, month ASC, 
         CASE 
            -- Place btype ='block' / bnumber = '1' at the first place
            WHEN btype ='block' and bnumber = '1' THEN 1
            -- Prioritize bnumber = '2' / btype <> 'block' over 
            -- bnumber = '2' / btype = 'block'
            WHEN bnumber = '2' THEN CASE 
                                      WHEN btype <> 'block' THEN 1.1
                                      ELSE 1.2
                                    END            
            ELSE bnumber  
         END ASC, 
         bnumber ASC, 
         btype ASC

Demo here

---

Related Topics