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) should be 1000,
How to implement the
If you know that
salaryplus will always be greater than
salaryminus, then you can do
order by coalesce(salaryplus, salaryminus, 0)
coalesce will return the first value which is not null, or (in this example) 0, if both values are null.
Otherwise, do something like this:
order by greatest(ifnull(salaryminus,0), ifnull(salaryplus,0))
This will treat both
salaryplus as 0 if they are null, and will order by the larger of the two.