Multiplying fields for results

[UVAdcock]

Alright, I know this is a simple one, but my brain's isn't working today.

I want to select records from a database that have a certain group name, and have only the top 10 highest price * average volume to show up - there are two separate fields, [FONT=courier new]price[/FONT] and [FONT=courier new]avg_vol[/FONT], but I want to order by the product of them, a value that doesn't exist in the database itself. Can MySQL do this calculation on the fly?

This is what I was thinking:
[FONT=courier new]SELECT FROM current WHERE group_name = "Banks-Northeast " ORDER BY (price avg_vol) DESC limit 10[/FONT]

TIA - Again,
Justin

[UVAdcock]

Well, after a triple skim latte, it suddenly came to me:

SELECT (price * avg_vol) AS product FROM current WHERE group_name = "Aerospace/Defense " ORDER BY product DESC LIMIT 0,50

Thanks for patience!

Justin

[Sxooter]

If MySQL doesn't do functional indexes, you might find that the performance degrading as the database grows. Postgresql offers functional indexes, which would let you do something like this:

create index current_priceXavg_vol on current (current_price*avg_vol);

which means that you could look them up with an index. So, even with 30,000 or more items to look through, the result should come back quickly.