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.
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
You can union all fields and order like this:
select price1 from vehicles WHERE to='$to' union all
select price2 from vehicles WHERE to='$to' union all
select price3 from vehicles WHERE to='$to' union all
select price4 from vehicles WHERE to='$to' union all
select price5 from vehicles WHERE to='$to'
order by price1