CODEWITHSUNDEEP
sqlsql-server
Rinoy Ashokan
Rinoy Ashokan

Reputation: 1619

In sql server I want to get class name ,student name who score maximum mark along with their Maximum mark.Table details given below

I have a StudentDetails table as below 

select * from StudentDetails

enter image description here

And we have Class table as below

select * from Class

enter image description here

I know by using group by and aggregate function we can display easily maximum marks obtained in each class by below query

select C.Name as 'ClassName',
       MAX(SD.Mark) as 'Max Mark' 
from   StudentDetails SD 
       inner join Class C on SD.classid = C.ClassId group by C.Name

giving below result

enter image description here

But now I need to display the student name also along with the maximum mark in each class by using group by and aggregate function. I used below query but it did not give expected result.

select C.Name as 'ClassName',
       SD.Name as 'Student Name', 
       Max(SD.Mark) as 'Max Mark' 
from   StudentDetails SD 
       inner join Class C on SD.classid = C.ClassId 
group by C.Name,SD.Name

giving below result .

enter image description here

This result is not as expected, I need a query which will result 

Division A , Mrx , 536
Division B , MrC , 535.

using group by and aggregate function Please somebody help me..

Upvotes: 0

Views: 1694

Answers (6)

Zorkolot
Zorkolot

Reputation: 2017

You can use a derived table. The derived table takes the ClassID with it's MAX(Mark). The receiving query and joins apply the appropriate descriptions.

SELECT C.Name
      ,SD.Name
      ,dT.MaxMark
  FROM ( 
         --Gets the MaxMark per ClassID
         SELECT S.ClassID
               ,MAX(Mark) [MaxMark]
           FROM StudentDetails S
         GROUP BY S.ClassID  
       ) AS dT INNER JOIN StudentDetails SD ON SD.Mark = dT.MaxMark
               INNER JOIN Class C ON C.ClassID = dT.ClassID

Gives output:

Name        Name    MaxMark
Division A  MrX     536
Division B  MrC     535

If you want to examine the full code: sqlfiddle

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to compare a student class result with the maximum class result. The easiest way to do this is with MAX OVER:

select classname, studentname, mark
from
(
  select
    c.name as classname,
    sd.name as studentname,
    sd.mark,
    max(sd.mark) over (partition by sd.class) as class_max_mark
  from studentdetails s
  join class c on c.classid = sd.classid
) analysed
where mark = class_max_mark;

If you are forced to use GROUP BY, then you'd want:

select
  c.name as classname,
  sd.name as studentname,
  sd.mark
from studentdetails s
join class c on c.classid = sd.classid
where (sd.classid, sd.mark) in
(
  select classid, max(mark)
  from studentdetails
  group by classid
);

SQL Server, however, doesn't support IN clauses with multiple columns. So you need a join which obfuscates the query slightly:

select
  c.name as classname,
  sd.name as studentname,
  sd.mark
from studentdetails s
join class c on c.classid = sd.classid
join
(
  select classid, max(mark) as max_mark
  from studentdetails
  group by classid
) m on m.classid = sd.classid and m.max_mark = sd.mark;

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

Try this:

select B.Name, A.Name, A.Mark from (
    select Name, Mark, ClassId, MAX() over (partition by ClassId) as MaxMark from StudentDetails
) as A join Class as C on A.ClassId = C.ClassId
where A.Mark = A.MaxMark

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

You may use a correlated subquery in WHERE

select C.Name as 'ClassName',
       SD.name,
       SD.Mark as 'Max Mark' 
from   StudentDetails SD 
inner join Class C on SD.classid = C.ClassId
where sd.mark = (select max(mark) from StudentDetails s where sd.classid = s.classid)

Upvotes: 0

Aswani Madhavan
Aswani Madhavan

Reputation: 816

Make use of dense_rank

select StudName, ClsName, Mark from 
  (
      select Dense_Rank() over(partition by #Table_studentDetails.classId 
       order by Mark desc) Rank, 
      #Table_studentDetails.Name StudName, #Table_Class.Name ClsName,Mark
      from #Table_studentDetails inner join #Table_Class on 
      #Table_studentDetails.classid = #Table_Class.classid

    )T
 where Rank = 1

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this :

WITH CTE AS(

     select C.Name as 'ClassName',
            MAX(SD.Mark) as 'Max Mark' 
     from   StudentDetails SD 
            inner join Class C on SD.classid = C.ClassId group by C.Name
)
SELECT STudentName,*
FROM CTE
INNER JOIN StudentDetails SD ON Marks = [Max Marks]

Upvotes: 1

Related Questions