CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ravi Shukla
Ravi Shukla

Reputation: 73

Want to find name and maximum marks scored subject from a table in sql server

Hi I have a table variable and I want to find every student name with there maximum scored subject.

declare @test1 table (name varchar(50), english int, math int, science int)

insert into @test1(name, english, math, science)
select 'A', 50, 90, 70
union all 
select 'B', 60, 80, 65
union all 
select 'C' , 80,65, 70
union all 
select 'D', 70, 75, 89

Please if any one can help me out, that would be appreciable.

Upvotes: 2

Views: 79

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81950

Since you don't want APPLY, perhaps UNPIVOT

Declare @test1 table (name varchar(50), english int, math int, science int)
insert into @test1(name, english, math, science)
select 'A', 50, 90, 70 union all select 'B', 60, 80, 65 union all  select 'C' , 80,65, 70 union all  select 'D', 70, 75, 89

Select Top 1 with Ties 
       Name,SubjectName,Score
From   @test1 A
unpivot ( Score for SubjectName in ( english, math, science) ) u
Order by Row_number() Over (Partition By Name Order By Score Desc)

Retruns

Name    SubjectName  Score
A       math         90
B       math         80
C       english      80
D       science      89

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93704

Here is one trick to do this 

SELECT TOP 1 WITH ties NAME,
                       sub_name,
                       mark
FROM   @test1
       CROSS apply (VALUES(english,'english'),
                          (math,'math'),
                          (science,'science'))tc(mark, sub_name)
ORDER  BY Row_number()OVER(partition BY NAME ORDER BY mark DESC)

or use ugly looking CASEstatement but this will perform better then Cross Apply

SELECT NAME,
       sub_name,
       CASE sub_name
         WHEN 'english' THEN english
         WHEN 'math' THEN math
         ELSE science
       END mark
FROM   (SELECT *,
               CASE
                 WHEN english > math AND english > science THEN 'english'
                 WHEN english < math AND math > science THEN 'math'
                 ELSE 'science'
               END AS sub_name
        FROM   @test1) a

Upvotes: 4

John Cappelletti
John Cappelletti

Reputation: 81950

Prdp's Cross Apply would be my first choice (+1). BUT, if you don't want to specify all the fields, here a a slightly more dynamic approach via XML and a CROSS APPLY (or two)

Declare @test1 table (name varchar(50), english int, math int, science int)
insert into @test1(name, english, math, science)
select 'A', 50, 90, 70 union all select 'B', 60, 80, 65 union all  select 'C' , 80,65, 70 union all  select 'D', 70, 75, 89

Select Top 1 with Ties 
       A.Name
      ,C.*
 From  @test1 A
 Cross Apply (Select XMLData= cast((Select A.* for XML Raw) as xml)) B
 Cross Apply (
                Select Item   = attr.value('local-name(.)','varchar(100)')
                      ,Value  = attr.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row') as A(r)
                 Cross Apply A.r.nodes('./@*') AS B(attr)
                 Where attr.value('local-name(.)','varchar(100)') not in ('name','otherfields')  --<< field names are case sensitive
             ) C
 Order by Row_number() Over (Partition By Name Order By Value Desc)

Returns

Name    Item    Value
A       math    90
B       math    80
C       english 80
D       science 89

Upvotes: 1

Related Questions