CODEWITHSUNDEEP
mysqlstored-proceduressql-order-bystored-functions
alec
alec

Reputation: 151

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:

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.

Upvotes: 0

Views: 134

Answers (2)

Christian
Christian

Reputation: 827

Try to use

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

Upvotes: 1

Ibrahim
Ibrahim

Reputation: 2072

You will have to wrap the main select into another select which will contain the order clause. Something like this:

SELECT * FROM (SELECT c.*, ((c.MPG * priority.MPG) + (c.Price * priority.Price) + (c.seats * priority.seats)) AS score FROM cars) x ORDER BY x.score

Upvotes: 0

Related Questions