复杂的MySQL SORT基于时间

Question

I have to sort MySQL records based on a unix time field. However, some records hold a value of 1 instead of time. Here's how I want them sorted, assuming current time is 125:

127
130
132
133
1
1
1
1
124
122
119

As you can see, the top section is records in ASC order by the time field with the soonest time first, these are followed by all the records that are marked with 1 instead of a proper time, and then the records from the past in DESC order. Is this doable?

Answer 1

You can do this with the following code:

drop table if exists numbers;
create table numbers ( value int );
insert into numbers values 
(119),(122),(124),(127),(130),(131),(1),(1),(1),(1);

select 
    value 
from numbers
order by if( value = 1, 125, if( value > 125, 99999 - value, value ) )  desc

which produces:

value
-----
127
130
131
1
1
1
1
124
122
119

Jus replace all occurrences of 125 with the current time and `99999' with the largest time value that you might encounter in your table.