15,030,863 members
See more:
Hi All,

I have a table data like this,

Name m1 m2 m3 m4
-----------------

N1 34 36 37 58
N2 35 31 45 67
N3 34 65 37 34
N4 34 78 09 58
N5 34 47 0 18
N6 89 36 56 60

I need the result as

Name Maximum Mark
---------------
N1 58
N2 67
N3 65
N4 78
N5 47
N6 89

How to do this in sql -server 2005
Posted

## Solution 1

You may try sql subquery.
here is an example
SQL
```SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;```

```Result:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6```

original table is
```Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20```

## Solution 2

This may not be the optimim way but it gives the output you want....

SQL
```select nname, max(M.MaximumMark) from (
select nname, m1 as MaximumMark from test_ union
select nname, m2 as MaximumMark from test_ union
select nname, m3 as MaximumMark from test_ union
select nname, m4 as MaximumMark from test_) AS M
GROUP BY M.nname```

## Solution 3

Here is another one

SQL
```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)

-- 1. [Name] is unique
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

-- 2. [Name] is NOT unique
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]```

## Solution 4

I am not tested it but try this
Quote:
Select Name ,

iif(m1>m2,iif(m1>m3,iif(m1>m4,m1,m4),iif(m3>m4,m3,m4),iif(m2>m3,iif(m2>m4,m2,m4),iif(m3>m4,m3,m4))))) as Maximum Mark

From TableName Order By (m1+m2+m3+4)