Thread: MySQL Queries

I am attempting to figure out how to write MySQL queries for two scenarios.

Given example data similar to this:

Name Val1 Val2 Val3
AAA 10 70 30
BBB 30 60 90
CCC 20 40 30


Scenario 1:
Get the top 3 rows by a combination of Val2 and Val3 (repeating is possible).
Expected Example Output:
BBB - 90
AAA - 70
BBB - 60

Scenario 2:
Get the top 3 rows by a combination of Val2 divided by Val1 and Val3 divided by Val1 (repeating possible).
Expected Example Output:
AAA - 7
AAA - 3
BBB - 3


Does anyone know how I would create a MySQL query to do that sort of thing? I know how to get ordered data based on one column, but not multiple columns, or by mathematics on multiple columns.

Try a UNION to combine two queries. For instance, in scenario 1, UNION two selects, the first gets Val1 and second Val2, then query this and order as appropriate. In scenario 2, have 2 subqueries perform the selects and math operations, UNION them, then select from and order as appropriate.
An example of the UNION

SELECT Name, Val1 as value FROM mytable UNION ALL select Name, Val2 AS value FROM mytable

You could perform the division as needed...

SELECT Name,  v2/v1 as div from (SELECT Val1 as v1, VAL2 as v2, name FROM mytable)

...and union the results of the 2 divisions, selecting and ordering as appropriate

copeg, I took your post as a jumping point and found the solution for my first problem (have not looked into the second yet).
In case anyone comes across this same problem, the solution for my first problem was:

(SELECT `Name`, `Val2` AS value FROM `someTable` ORDER BY `Val2` DESC LIMIT 3) UNION (SELECT `Name`, `Val3` AS value FROM `someTable` ORDER BY `Val3` DESC LIMIT 3) ORDER BY value DESC LIMIT 3

The statement in the first parentheses orders the items in Val2 and sets the ordered list with the name: "value". The statement in the second parentheses does the same with Val3. The UNION combines the two into one result. And the final ORDER BY statement, where it orders by "value", orders the UNIONed result by the combination of Val2 and Val3 (with the result set column alias being 'value').
And, it DOES allow for repeats.

I'm going to see if I can hammer out the second statement later today.