SQL only use record with higher value

Michael Graham
Michael Graham used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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

Michael GrahamData Analyst

Author

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
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
It would help if you could post your entire query.  Mind readers we ain't.
Ensure you’re charging the right price for your IT

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

Michael GrahamData Analyst

Author

Commented:
Assuming I am only using the records with the highest revenue as stated before.
Michael GrahamData Analyst

Author

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
Michael GrahamData Analyst

Author

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

Mike
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial