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
精彩评论