What Mysql query will return this result?

Question

I have two tables A,B which are related to each other (simplified):

A:
+-------+---------+ 
| id    | type    | 
+-------+---------+ 
| 1     | apple   | 
| 2     | orange  | 
| 3     | banana  | 
+-------+---------+ 

B:
+-------+---------+-----------+ 
| id    | a_id    |  rank     |  
+-------+---------+-----------+  
| 1     | 1       |  9.9      |
| 2     | 1       |  7.7      |
| 3     | 2       |  3.3      |
| 4     | 2       |  8.8      |
| 5     | 2       |  1.1      |  
| 6     | 3       |  3.3      |
| 7     | 3       |  2.2      | 
| 8     | 1       |  0.0      | 
+-------+---------+-----------+   

What mysql query will return the following result?

Result
+-------+---------+-----------+ 
| id    | type    |  rank     |  
+-------+---------+-----------+  
| 1     | apple   |  0.0      | 
| 2     | orange  |  1.1      | 
| 3     | banana  |  2.2      | 
+-------+---------+-----------+ 

The rank that was inserted last in table B is picked (it's not MAX(rank)).

The rank in the result table needs to be picked from table B with the highest id.

Answer 1

UPDATED

You may want to try joining with a subquery to get the MAX(id) for each a_id in table_b, and then INNER JOIN with table_b to get the rank:

SELECT   ta.id, 
         ta.type,
         tb.rank
FROM     table_a ta
JOIN     (
            SELECT   MAX(id) AS id, 
                     a_id
            FROM     table_b
            GROUP BY a_id
         ) sub_q ON (sub_q.a_id = ta.id)
JOIN     table_b tb ON (tb.id = sub_q.id)
ORDER BY ta.id;

Test case:

CREATE TABLE table_a (id int, type varchar(10));
CREATE TABLE table_b (id int, a_id int, rank decimal(2,1));

INSERT INTO table_a VALUES (1, 'apple');
INSERT INTO table_a VALUES (2, 'orange');
INSERT INTO table_a VALUES (3, 'banana');

INSERT INTO table_b VALUES (1, 1, 9.9);      
INSERT INTO table_b VALUES (2, 1, 7.7);       
INSERT INTO table_b VALUES (3, 2, 3.3);       
INSERT INTO table_b VALUES (4, 2, 8.8);      
INSERT INTO table_b VALUES (5, 2, 1.1);         
INSERT INTO table_b VALUES (6, 3, 3.3);       
INSERT INTO table_b VALUES (7, 3, 2.2);       
INSERT INTO table_b VALUES (8, 1, 0.0);      

Result:

+------+--------+------+
| id   | type   | rank |
+------+--------+------+
|    1 | apple  |  0.0 |
|    2 | orange |  1.1 |
|    3 | banana |  2.2 |
+------+--------+------+
3 rows in set (0.01 sec)

Answer 2

What about this:

SELECT a.id, a.type, b.rank 
FROM tempa a, tempb b 
WHERE a.id = b.a_id 
  AND b.id = (
    SELECT MAX(b.id) 
    FROM tempb b 
    WHERE b.a_id = a.id
)
ORDER BY a.id;

Outputs:

1, apple, 9.50
2, orange, 1.10
3, banana, 5.50

Answer 3

SELECT a.id,
       a.type,
       b1.rank
 FROM a,
      b b1
 WHERE b1.a_id = a.id
 AND NOT EXISTS( SELECT b2.id
                  FROM b AS b2
                 WHERE b2.a_id = a.id
                   AND b2.id > b1.id
               )
 ORDER BY a.type