How to order by maximum of two column which can be null in MySQL?

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?

---

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.

---

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.

---

Just found another way to solve this.

And will be supported by other SQLs as well:

SELECT * FROM jobs 
ORDER BY CASE 
    WHEN salaryminus > salaryplus THEN salaryminus
    ELSE salaryplus
END