SQL sorting by two columns, ignore null

Question

I have a table with two columns like so:

price (varchar)
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?

If the final price is price - discount, then shouldn't you ORDER BY price - discount ASC? — Patrick Q
– Patrick Q, Commented Jun 3, 2014 at 2:14
agree with @PatrickQ, perhaps ORDER BY price-ifnull(discount,0) is more appropriate — Fabricator
– Fabricator, Commented Jun 3, 2014 at 2:17
@PatrickQ actually discount holds the new price of the item when the discount is applied, e.g. product 2 would be $10, not $15. — Liftoff
– Liftoff, Commented Jun 3, 2014 at 19:39

sgeddes · Accepted Answer · 2014-06-03 02:13:42Z

6

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.

answered Jun 3, 2014 at 2:13

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Liftoff Over a year ago

Does my column in my table have to be NULL or NOT NULL for this to work?

sgeddes Over a year ago

@David -- for those records where discount exists, this will order by that amount, followed by the original price (which may not be needed), and then the title. If the discount is null, this will sort by price then title.

Liftoff Over a year ago

Ah I see. I had to update those to be NULL instead of an empty string first. Thanks so much!

Suresh · Accepted Answer · 2014-06-03 02:25:08Z

1

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

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

answered Jun 3, 2014 at 2:25

Suresh

1,1691 gold badge16 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL sorting by two columns, ignore null

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related