mysql - Suborder where fields are 0 -

so can't find answer question, have following query:

select * bans     removed = 0 ,     (         (`time` + `length` - unix_timestamp()) > 0     or         length = 0     ) order length = 0,(`time` + `length` - unix_timestamp()) 

it gets bans table , orders them according when going unbanned. bans length of 0 permanent banned , should @ bottom of list. works. i'd permanent bans ordered descending banid. doesn't default ordered ascending.

and if put asc in hole order screwed

database:

create table if not exists `rp_bans` (   `banid` int(11) not null auto_increment,   `steamid` text not null,   `name` text not null,   `adminid` text not null,   `aname` text not null,   `reason` text not null,   `time` int(11) not null,   `length` int(11) not null,   `removed` tinyint(1) not null,   primary key (`banid`) ) 

example data:

id  time    length  current time    timeleft 1   1365410228  0       1365411228  -1000 2   1365410229  3600    1365411228  2601 3   1365410230  0       1365411228  -998 4   1365410231  84000   1365411228  83003 5   1365410232  3600    1365411228  2604 6   1365410233  0       1365411228  -995 7   1365410234  800000  1365411228  799006 

expected result:

id  time    length  current time    timeleft 2   1365410229  3600    1365411228  2601 5   1365410232  3600    1365411228  2604 4   1365410231  84000   1365411228  83003 7   1365410234  800000  1365411228  799006 6   1365410233  0       1365411228  -995 3   1365410230  0       1365411228  -998 1   1365410228  0       1365411228  -1000 

so if length not 0 order timeleft ascending (which calculated time + length - current time). when length 0 needs ordered id descending behind entries length not 0.

you split it, , union them:

(select * rp_bans length > 0 order id asc limit 10)  union (select * rp_bans length = 0 order id desc limit 10) 

you have limit them; docs:

however, use of order individual select statements implies nothing order in rows appear in final result because union default produces unordered set of rows. therefore, use of order in context typically in conjunction limit, used determine subset of selected rows retrieve select, though not affect order of rows in final union result.

i found taking limit higher result works well.