MySQL Order By date column and integer column, but specify ordering rules of integer column?
You can order the query by a calculated expression, e.g., case:
SELECT *FROM `my_table`ORDER BY CASE `integer` WHEN 2 THEN 1 ELSE 0 END DESC, `date` ASC
The easiest way to do this is subtract your integer by 2 and then get the absolute value of that number. Then sort on that. The absolute value of 2 - 2 will always be zero and any other calculation will be greater than zero. therefor you will be forcing integers of 2 to the top of the list (SQL Fiddle):
SELECT * FROM myTableORDER BY ABS(`integer` - 2), `date`
Doing this with a UNION was trickier than I first thought, but you can specify a priority:
SELECT 1 as priority, id, mynumber, date from myTableWHERE mynumber = 2UNIONSELECT 2, id, mynumber, date from myTableWHERE mynumber <> 2ORDER BY priority ASC, date ASC