How to extract a row with the maximal value?

I have two tables and want to do the following. From the first table I extract all rows that fulfill a given condition. For all extracted rows I take a value from the first column. For every value I extract all raws from the second table that contain the given value in the first column. All raws extracted from the second table have to be ordered in a specific way and I want to take only one row that is the first (according to the ordering criteria). This is my problem, I do not know how to take one row with the highest value of the ordering criteria.

ADDED

The first table:

| aaa | Bob |
| bbb | Eva |
| ccc | Bob |

The second table:

| aaa | 111 | 1 |
| aaa | 342 | 2 |
| ccc | 576 | 1 |
| ccc | 749 | 3 |

From the first table I take all rows containing Bob. The first column in these rows contain the following values: "aaa" and "ccc". For "aaa" in the second table I have two rows and I want to take those that has the maximal value in the last column. So, I have |aaa|342|2|. The same I do with the "ccc". I take this row |ccc|749|3|. And finally I want to have the two rows ordered according to the value of the last column.

ADDED 2

I have just realized that the essence of the problem is as following. In a given table开发者_JS百科 I want to replace all rows containing the same value in the first columns by one "representative" row (that has the maximal value in the third column). In more details, I want to replace this table:

| aaa | 111 | 1 |
| aaa | 342 | 2 |
| ccc | 576 | 1 |
| ccc | 749 | 3 |

By this one:

| aaa | 342 | 2 |
| ccc | 749 | 3 |

select t2.* 
  from t1
  join t2 on t2.id = (select t2c.id 
                        from t2 as t2c 
                       where t2c.t1_id = t1.id 
                       order by t2c.val desc
                       limit 1)
 where t1.name = 'Bob'
 order by t2.val

You did not specify column names, so i made them up.

t2.id has to be unique.

This can be achieved using correlated subqueries but the performance will be abysmal.

mysql> select * from user;
+--------+-----------+
| userid | user_name |
+--------+-----------+
| aaa    | Bob       |
| bbb    | Eva       |
| ccc    | Bob       |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> select * from user_data;
+--------+-------+------+
| userid | value | num  |
+--------+-------+------+
| aaa    |   111 |    1 |
| aaa    |   342 |    2 |
| ccc    |   576 |    1 |
| ccc    |   749 |    3 |
| bbb    |  1000 |    1 |
| bbb    |   800 |    2 |
+--------+-------+------+
6 rows in set (0.00 sec)

mysql> SELECT u1.userid,u1.user_name,ud1.value from user_data ud1 join user u1 ON ud1.userid=u1.userid where ud1.value=(SELECT value FROM user_data ud2 WHERE ud2.userid=ud1.userid ORDER BY value desc LIMIT 1) AND u1.user_name='Bob';
+--------+-----------+-------+
| userid | user_name | value |
+--------+-----------+-------+
| aaa    | Bob       |   342 |
| ccc    | Bob       |   749 |
+--------+-----------+-------+
2 rows in set (0.00 sec)