user3509258 user3509258 - 7 months ago 24
MySQL Question

Order mysql table rows by value from generated column

MYSQL rookie here, so please bear with me.

I have a table called

that stores data about cars:

  • Make

  • Model

  • MPG

  • Price

  • number of seats

And I have a table called
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
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.


Try to use

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