mySQL statement ORDER BY multiple columns

Question

If I have two columns, prices (full_price, sales_price), and these columns has numbers in them. I know that sql statements can do multiple order by's but how does it work when these columns have values in them?

("SELECT * FROM table ORDER BY full_price,sales_price DESC")

How do I accomplish so it will pick the smallest value of the two columns? and it will place the data in order based on the chosen column between full_price and sales_price?

Note: sometimes sales_price will have no value.

Thanks

Edit:

Example

id    full_price        sales_price
1      23                 42           
2      342                200
3      1
4      10                 8

What I'm trying to do is with these numbers, I can output data associated with the lowest price.

The order should be:

3,4,1,2

Reason:

I edited to show an example, thank you

hellomello
– hellomello

2012-07-25 05:10:32 +00:00
Commented Jul 25, 2012 at 5:10 — hellomello
– hellomello, Commented Jul 25, 2012 at 5:10

mu is too short · Accepted Answer · 2012-07-25 05:15:06Z

4

Assuming that your blank sales_price is a NULL and that full_price cannot be NULL:

select ...
from ...
where ...
order by case
    when sales_price is null then full_price
    else least(full_price, sales_price)
end

You probably want to add a secondary sort key to get consistent and sensible results from ties.

answered Jul 25, 2012 at 5:15

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

hellomello Over a year ago

how would i add another sort key? do i add it into the case or is it after the end?

mu is too short Over a year ago

@andrewliu: You'd add the second sort key after then end: order by case ... end, some_other_column

juergen d · Accepted Answer · 2012-07-25 05:12:35Z

1

SELECT * FROM table
ORDER BY case when sales_price is null or full_price is null
              then 0
              when full_price > sales_price
              then sales_price ASC
              else full_price ASC
         end

answered Jul 25, 2012 at 5:12

juergen d

205k40 gold badges305 silver badges377 bronze badges

Comments

Omesh · Accepted Answer · 2012-07-25 05:12:36Z

0

If you want results based on difference of full_price and sales_price then try:

SELECT *
FROM table
ORDER BY ABS(full_price - IF(sales_price IS NULL, 0, sales_price)) ASC

or if you want results based on comparision of full_price and sales_price then try:

SELECT *
FROM table
ORDER BY IF(full_price < IF(sales_price IS NULL, 0, sales_price), 
            full_price , sales_price ) ASC

answered Jul 25, 2012 at 5:12

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

mySQL statement ORDER BY multiple columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related