How to order by maximum of two column which can be null 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) should be 1000,

How to implement the maxof?

Answer 1

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 salaryminus and salaryplus as 0 if they are null, and will order by the larger of the two.

Answer 2

You can use coalesce to turn possibly-null columns into specific values instead -- e.g., coalesce(salaryminus, -99999999) will give a large negative number if salaryminus is null, but will return salaryminus if not null.