I am using SQL to create a query which is basically a big case when statement involving a lot of codes. However, there can be two records for some of the people in the database.
John | $100|
Mike | $200|
Bob | $50|
Bob | $300|
In the above example "Bob" appears twice. In these cases I only want to use the record where the Revenue is highest. So in this case I only want to use the record where the Revenue is $300.
Most of our records only have 1 name so most of the time there is just "John" or "Mike"
Is there a way to do this in SQL?
Thanks for any help,