SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table MyTable
(
  ID int,
  CName int,
  Tot_Val int,
  PName nvarchar(2)
);

insert into MyTable (ID, CName, Tot_Val, PName) values
(1, 1, 100, 'P1'),
(2, 1, 10 , 'P2'),
(3, 2, 50 , 'P2'),
(4, 2, 80 , 'P1');
4 rows affected

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;


ID
CName
Tot_Val
PName
No


1
1
100
P1
1

2
1
10
P2
2

4
2
80
P1
1

3
2
50
P2
2


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;


ID
CName
Tot_Val
PName
No


1
1
100
P1
1

4
2
80
P1
1


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;


ID
CName
Tot_Val
PName
No


1
1
100
P1
1

4
2
80
P1
1