How to order a maximum of two columns that can be empty in MySQL?

Question

create table jobs( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, ..... salaryminus INTEGER UNSIGNED DEFAULT NULL, salaryplus INTEGER UNSIGNED DEFAULT NULL, ..... ); 

I want to do something like:

 Select * from jobs order by maxof(salaryminus, salaryplus) limit 10; 

maxof(Null,1000) must be 1000,

How to implement maxof ?

Answer 1

If you know that salaryplus will always be more than salaryminus , you can do

 order by coalesce(salaryplus, salaryminus, 0) 

coalesce will return the first value that is not null or (in this example) 0 if both values ​​are zero.

Otherwise, do the following:

 order by greatest(ifnull(salaryminus,0), ifnull(salaryplus,0)) 

This will apply to both salaryminus and salaryplus to 0 if they are zero, and will be ordered by the larger of the two.

Answer 2

You can use coalesce to instead include null columns in specific values ​​- for example, coalesce(salaryminus, -99999999) will give a large negative number if rankminus is null but returns returnminmin if not null.