sorting - I need to sort MYSQL results by a number passed in OR another column -

here table select statement: sqlfiddle want group job_id , sort number passed in, (in case number 2) if there no match show result default marked true if none marked true sort production_days desc

the end result should have:

all jobs show column production_days = 2 except 103 did not have matching column, sorted is_default desc

i have tried

select *, case production_days  when 2 0  when is_default = 1 1  else 2  end sort `jobs` group job_id order job_id, sort 

edit: desired results are: rows unique job_id have number 2 in production_days column if job_id not have 2 in column looking 1 in is_default column. not care how results achieved in 1 select statement.

i going rephrase understand it: want sort jobs have production_days = 2 first, jobs is_default true, , other jobs.

to that, first broke query down 3 groups , put them together. started getting ones had value 2 in production_days, easiest. hardcoded 0 @ end demonstrate first group:

select j.*, 0 jobs j j.production_days = 2; 

then, found jobs had is_default = 1 did not have production_days of 2, this:

select j.*, 1 jobs j j.is_default = 1  , j.job_id not in (   select job_id   jobs   production_days = 2); 

for last group, need check jobs aren't in either of previous lists. simplifies bit because can narrow down requirement projects have production_days of 2, or have is_default = 1:

select j.*, 2 jobs j j.job_id not in(   select job_id   jobs   is_default = 1   or production_days = 2); 

the last step combine , sort results. use union, , order sortposition this:

select j.*, 0 sortposition jobs j j.production_days = 2 union select j.*, 1 sortposition jobs j j.is_default = 1  , j.job_id not in (   select job_id   jobs   production_days = 2) union select j.*, 2 sortposition jobs j j.job_id not in(   select job_id   jobs   is_default = 1   or production_days = 2) order job_id, sortposition; 

here sql fiddle example.