NDV NDV - 3 months ago 23
MySQL Question

Order multiple columns ASC

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.

Answer

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
Comments