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
Michael GrahamData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That would be a pretty simple MAX statement. For more info on how this works check out SQL Server GROUP BY .
SELECT Name, MAX(Revenue) as Revenue
FROM YourTable
GROUP BY Name

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael GrahamData AnalystAuthor Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
It would help if you could post your entire query.  Mind readers we ain't.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Michael GrahamData AnalystAuthor Commented:
Assuming I am only using the records with the highest revenue as stated before.
0
Michael GrahamData AnalystAuthor Commented:
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
0
Michael GrahamData AnalystAuthor Commented:
Thank you - I used a max clause in a subquery which got me the results.

Mike
0
PortletPaulEE Topic AdvisorCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.