Order multiple columns ASC

Question

My database layout:

ID Description           Shop 1  Shop 2  Shop 3
1 | Article Description | 19.99 | 29.99 | 4.99
2 | Article Description | 45.94 | 14.54 | 87.51
3 | Article Description | 75.54 | 12.58 | 45.87

My question: How can I re order the columns so the lowest price appear in the first column, the second one in the second ETC..?

I want to get this output (Price order):

ID: 1 - 4.99 - 19.99 - 29.99  
ID: 2 - 14.54 - 45.94 - 87.51  
ID: 3 - 12.58 - 45.87 - 75.54

Thanks.

This should be done after you've fetched records from database. In PHP. — Alok Patel, Aug 10 '16 at 11:14
Possible duplicate of [php/mysql multiple order by](http://stackoverflow.com/questions/38868833/php-mysql-multiple-order-by) — e4c5, Aug 10 '16 at 11:16
You should change the structure of your database: Put the shops and the prices in different tables and join them to get the information you need in the order you need it. This does not scale very well, for example when you want to add or remove a shop. — jeroen, Aug 10 '16 at 11:21

score 2 · Accepted Answer · answered Aug 10 '16 at 11:21

I think its best to do it via PHP on the application layer , but you can do it with LEAST() and GREATEST() :

SELECT t.id,t.description,
       LEAST(t.shop1,t.shop2,t.shop3) as lowest,
       (t.shop1+t.shop2+t.shop3) - (LEAST(t.shop1,t.shop2,t.shop3) + GREATEST(t.shop1,t.shop2,t.shop3)) as middle_one
       GREATEST(t.shop1,t.shop2,t.shop3) as biggest
FROM YourTable t

Order multiple columns ASC

1 Answers1

Linked