SQL only use record with higher value
Hello,
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.
For Example:
Name |Revenue|
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,
Mike
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.
For Example:
Name |Revenue|
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,
Mike
Data Analyst
Thank you. I should have been more specific in my question. There are other fields in the table like location, zip code, groupid, and count (count is always equal to 1).
If I wanted to do an aggregate function to get the sum of the count is there any way to do that?
Thanks again,
Mike
If I wanted to do an aggregate function to get the sum of the count is there any way to do that?
Thanks again,
Mike
Data Analyst
select case when ID is in ('50x', '20b') then 'New York'
when ID is in ('1', '2', '3') then 'Boston' end as 'City'
from here I would like to be able to sum the count in the database (count is always equal to 1) only when the revenue is at its maximum value
Thanks again
when ID is in ('1', '2', '3') then 'Boston' end as 'City'
from here I would like to be able to sum the count in the database (count is always equal to 1) only when the revenue is at its maximum value
Thanks again
