How do I join tables to choose the maximum row in the joined table?

Question

I have two tables and I would like to left join them on an attribute, but rather than taking all matching rows in the second table I would like to choose only the entry with the highest number (latest date, etc.) in a certain column. How do I express this result in SQL?

Here's a simplified example to clarify my question.

Table `colors`
| color |
+-------+
| red   |
| green |
| blue  |


Table `inventory`
| color | value | shape    |
+-------+-------+----------|
| red   | 1     | square   |
| red   | 2     | circle   |
| green | 7     | triangle |


Desired output:
| color | value | shape    |
+-------+-------+----------|
| red   | 2     | circle   |
| green | 7     | triangle |
| blue  | NULL  | NULL     |

My tables are fairly large so ideally a solution would be reasonably efficient. (There's no need for fine tuning, just trying to avoid double joins which could become huge.)

Answer 1

http://sqlfiddle.com/#!9/0b75c/6

SELECT c.*, i.value, i.shape
FROM colors c
LEFT JOIN inventory i
ON c.color = i.color
LEFT JOIN inventory i_
ON i.color = i_.color
  AND i.value<i_.value
WHERE  i_.color IS NULL

http://sqlfiddle.com/#!9/0b75c/8

SELECT i.value, i.shape
FROM inventory i
LEFT JOIN inventory i_
ON i.color = i_.color
  AND i.value<i_.value
WHERE  i_.color IS NULL