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
?
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.
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.
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,...)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With