Avatar of Michael Graham
Michael Graham
Flag for United States of America asked on

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
Microsoft SQL Server

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Michael Graham

ASKER
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
Jim Horn

It would help if you could post your entire query.  Mind readers we ain't.
Michael Graham

ASKER
Assuming I am only using the records with the highest revenue as stated before.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Michael Graham

ASKER
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
Michael Graham

ASKER
Thank you - I used a max clause in a subquery which got me the results.

Mike
PortletPaul

I suggest you review answers involving ROW_NUMBER() OVER()

When these are used in combination it is possible to pull complete rows representing "highest" or "latest" etc. without using aggregate functions.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.