Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sorting

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?

like image 451
Mask Avatar asked Oct 23 '09 05:10

Mask


People also ask

Can I ORDER BY 2 columns MySQL?

After the ORDER BY keyword, add the name of the column by which you'd like to sort records first (in our example, salary). Then, after a comma, add the second column (in our example, last_name ). You can modify the sorting order (ascending or descending) separately for each column.

Can we use ORDER BY for 2 columns?

You can also ORDER BY two or more columns, which creates a nested sort . The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence.

How do I find the maximum value of two columns in SQL?

The MySQL Solution If you're working with MySQL, you can combine MAX() with the GREATEST() function to get the biggest value from two or more fields. Here's the syntax for GREATEST: GREATEST(value1,value2,...)


1 Answers

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.

like image 115
Ian Clelland Avatar answered Oct 23 '22 12:10

Ian Clelland