Welcome to the Java Programming Forums

The professional, friendly Java community. 21,500 members and growing!

The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.

>> REGISTER NOW TO START POSTING

1. 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.

2. Re: MySQL Queries

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

3. Re: MySQL Queries

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.