Retrieve highest value from sql table

Question

How can retrieve that data:

Name     Title      Profit
Peter    CEO        2
Robert   A.D        3
Michael  Vice       5
Peter    CEO        4
Robert   Admin      5
Robert   CEO        13
Adrin    Promotion  8
Michael  Vice       21
Peter    CEO        3
Robert   Admin      15

to get this:

Peter........4
Robert.......15
Michael......21
Adrin........8

I want to get the highest profit value from each name. If there are multiple equal names always take the highest value.

In the future, please don't stuff your sample data with meaningless ....... - white space is actually a lot more useful. — Aaron Bertrand
– Aaron Bertrand, Commented Jul 21, 2012 at 23:41
@Aaron But then its not displayed like a table. I just meant it in a good way to help those who regard it. I changed the first part and removed the .... Do you like the result? — Elisabeth
– Elisabeth, Commented Jul 22, 2012 at 8:59

hkutluay · Accepted Answer · 2012-07-21 21:33:38Z

5

select name,max(profit) from table group by name

answered Jul 21, 2012 at 21:33

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaron Bertrand · Accepted Answer · 2012-07-21 23:38:53Z

2

Since this type of request almost always follows with "now can I include the title?" - here is a query that gets the highest profit for each name but can include all the other columns without grouping or applying arbitrary aggregates to those other columns:

;WITH x AS 
(
  SELECT Name, Title, Profit, rn = ROW_NUMBER()
    OVER (PARTITION BY Name ORDER BY Profit DESC)
  FROM dbo.table
)
SELECT Name, Title, Profit
FROM x
WHERE rn = 1;

answered Jul 21, 2012 at 23:38

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

1 Comment

Elisabeth Over a year ago

Actually you are right I have to include more fields. But then the solution of hkutluay does not work, but yours does. But I can not change the question or that would not be fair therefore I flagged hkutluay`s answer as solution. Gave you a point and thank you, the CTE with the Over clause is interesting to learn.

Collectives™ on Stack Overflow

Retrieve highest value from sql table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related