How to find Max value in a column in SQL Server 2012

I want to find the max value in a column

  ID       CName   Tot_Val  PName
  --------------------------------
  1        1       100      P1
  2        1       10       P2
  3        2       50       P2
  4        2       80       P1

Above is my table structure. I just want to find the max total value only from the table. In that four row ID 1 and 2 have same value in CName but total val and PName has different values. What I am expecting is have to find the max value in ID 1 and 2

Expected result:

 ID       CName   Tot_Val   PName
 --------------------------------
  1        1       100      P1
  4        2       80       P1

I need result same as like mention above

 select Max(Tot_Val), CName 
 from table1  
 where PName in ('P1', 'P2')
 group by CName

This is query I have tried but my problem is that I am not able to bring PName in this table. If I add PName in the select list means it will showing the rows doubled e.g. Result is 100 rows but when I add PName in selected list and group by list it showing 600 rows. That is the problem.

Can someone please help me to resolve this.

Answer

One possible option is to use a subquery. Give each row a number within each CName group ordered by Tot_Val. Then select the rows with a row number equal to one.

select x.*
from ( select mt.ID,
              mt.CName,
              mt.Tot_Val,
              mt.PName,
              row_number() over(partition by mt.CName order by mt.Tot_Val desc) as No
       from MyTable mt ) x
where x.No = 1;

An alternative would be to use a common table expression (CTE) instead of a subquery to isolate the first result set.

with x as
(
  select mt.ID,
         mt.CName,
         mt.Tot_Val,
         mt.PName,
         row_number() over(partition by mt.CName order by mt.Tot_Val desc) as No
  from MyTable mt
)
select x.*
from x
where x.No = 1;

See both solutions in action in this fiddle.