Maximum revenue from a given ID
Question
Suppose you're given the following table, showing revenue values associated with different IDs. Note how an ID can have multiple revenue values:
Table: earnings
| id | revenue | content |
|---|---|---|
| 2 | 100 | Here is a description of the sale |
| 1 | 200 | Here is a good description of the sale |
| 1 | 400 | Here is another description of the sale |
| 2 | 250 | Make sure you return some content |
| 3 | 50 | A sale for you |
| 3 | 45 | Another sale |
| 4 | 100 | Describing descriptively |
Using this table, write a SQL query to return the max revenue for each ID. Note that this isn't as simple as it might appear at first glance!