user3509258 user3509258 - 4 months ago 10
MySQL Question

Order mysql table rows by value from generated column

MYSQL rookie here, so please bear with me.

I have a table called

Cars
that stores data about cars:


  • Make

  • Model

  • MPG

  • Price

  • number of seats



And I have a table called
priority
that stores how important each car characteristic is to me on a 0-5 scale. So this table might look like:


+-----+-------+-------+
| MPG | Price | Seats |
+-----+-------+-------+
| 0 | 2 | 3 |
+-----+-------+-------+


I want to be able to generate a score for each
Car
like follows:

score = (car.MPG * priority.MPG) + (car.Price * priority.Price) + (car.seats * priority.seats)


I want to essentially do a
Select * from cars order by score
. But I'm not sure how to form this query.

Help is greatly appreciated.

Answer

Try to use

select a.* from cars a, priority b order by ((a.mpg* b.mpg)+(a.price*b.price)+(a.seats*b.seats))