Retrieving rows with the highest value

Question

I have this query which returns all rows from a table:

select Cost, Name, Id
from #Table

It returns a result set that looks like this:

Cost   Name    Id
----   ----   ----
-2.00  Item1  1 
4.00   Item2  1 
6.00   Item3  1 
3.00   Item1  2
9.00   Item4  2

What I want to do is grab a row from each ID with the highest value, so the 5 results become two results:

Cost   Name    Id
----   ----   ----
6.00   Item3  1 
9.00   Item4  2

6.00 is the highest Cost from the Id of 1, and 9.00 is the highest cost from the Id of 2.

How would I change the query to do this?

Answer 1

select id, max(cost) MaxCost FROM #Table group by ID

to get the item name, you will need to join the result from this to the original, but remember, that there might be more than 1 set of original values for the combination found.

Answer 2

Something like this.

select max(cost) Cost, Name, Id
FROM #Table 
group by ID, Name

Answer 3

select max(cost),name from tablename group by id,name