Click here to Skip to main content
15,030,863 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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
   
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
   
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]
   
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)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900