根据MySQL中ORDER BY的可用性，考虑多列的值

Question

I have 10 price categories.

I have coded everything so that the correct price gets displayed in the price field according to availability. That part works perfectly.

But I don't know how to set the ORDER BY in the query so that all the price fields get into consideration before being sorted.

I have these fields:

'price1`, 'price2`, 'price3`, 'price4`, 'price5`, 'price6`, 'price7`, 'price8`, 'price9`, 'price10`

My current query looks like this:

SELECT * FROM vehicles WHERE `to`='$to' ORDER BY `price1`;

This works when there's only fields from price1 being displayed. however if there's a mixed result of price1 and other price fields, it still only takes price1 into consideration.

I also tried:

SELECT * FROM vehicles WHERE `to`='$to' ORDER BY `price1`, `price2`, `price3`, `price4`, `price5`, `price6`, `price7`, `price8`, `price9`, `price10`;

which failed obviously.

How can I utilize ORDER BY for all these fields at the same time?

Any help is appreciated. Thank you kindly.

Answer 1

SELECT * FROM vehicles WHERE `to`='$to' ORDER BY GREATEST(`price1`, `price2`, `price3`, `price4`, `price5`,`price6`,`price7`, `price8`, `price9`, `price10`) DESC

The above should order from the greatest value found in any of the "price" columns all the way down to the smallest value of the row's greatest column.

Say you have a table

 == Table structure for table test

|------
|Column|Type|Null|Default
|------
|id|int(11)|No|
|price1|int(11)|No|
|price2|int(11)|No|
|price3|int(11)|No|
== Dumping data for table test

|1|10|20|10
|2|30|10|10
|3|100|20|10
|4|10|10|60

The query

SELECT * FROM `test` ORDER BY GREATEST(`price1`, `price2`, `price3`) DESC

Will output

== Dumping data for table test

|3|100|20|10
|4|10|10|60
|2|30|10|10
|1|10|20|10