CODEWITHSUNDEEP
mysqlsqlsorting
Liftoff
Liftoff

Reputation: 25392

SQL sorting by two columns, ignore null

I have a table with two columns like so:

  1. price (varchar)
  2. discount (varchar)

These two hold the price and discount for all of the products in my database. When users view the products, I have multiple sorting modes available, one of which is Sort by price: low to high. In order to do this, I have the following code:

$stmt = $link->prepare("SELECT ... ORDER BY `discount` ASC, `price` ASC, `title` ASC");

This works fine for all rows where discount is defined, but in rows where discount is empty, they are sorted above the others, regardless of the value of price.

For example:

id|price|discount
-----------------
 1|20   |10
 2|25   |10
 3|15   |
 4|15   |

Will echo out in the order:

3, 4, 1, 2

How can I rewrite this statement to sort by price when discount has no value?

Upvotes: 4

Views: 2412

Answers (2)

Suresh
Suresh

Reputation: 1169

try with IFNULL? , i have created sample query on SQLfiddle

select * from product order by IFNULL(discount,price)

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

You can use COALESCE for this:

SELECT ... 
ORDER BY COALESCE(`discount`, `price`), `price`, `title`

This will first order by discount if it's not null, and then order by price. For those items with the same discount, it will then order by the price, followed by the title.

Note: Depending on your desired results, you may want to remove the additional order by price.

Upvotes: 6

Related Questions