Here is another one
if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp
(
[Name] varchar(10),
[m1] int,
[m2] int,
[m3] int,
[m4] int
)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34, 36, 37, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N2', 35, 31, 45, 67)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N3', 34, 65, 37, 34)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N4', 34, 78, 09, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N5', 34, 47, 0, 18)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N6', 89, 36, 56, 60)
select [Name],
case
when [m1] > [m2] and [m1] > [m3] and [m1] > [m4] then [m1]
when [m2] > [m1] and [m2] > [m3] and [m2] > [m4] then [m2]
when [m3] > [m1] and [m3] > [m2] and [m3] > [m4] then [m3]
else [m4]
end as MaximumMark
from #temp
select [Name],
max(
case
when [m1] > [m2] and [m1] > [m3] and [m1] > [m4] then [m1]
when [m2] > [m1] and [m2] > [m3] and [m2] > [m4] then [m2]
when [m3] > [m1] and [m3] > [m2] and [m3] > [m4] then [m3]
else [m4]
end
) as MaximumMark
from #temp
group
by [Name]