How to get the name of a the student who got max marks in each subject?

Question

I have the following table

 Name  |  Subject  | Marks
 --------------------------
 a        M          20
 b        M          25  
 c        M          30
 d        C          44 
 e        C          45
 f        C          46 
 g        H          20

Here I have a "Student" table I want to get the Name of the student who got
Max marks from each subject from the student table like the following OUTPUT.

 Name | Subject | Marks  
 c        M        30
 f        c        46
 g        h        20

Perspicacity

Answer 1

You can use the ROW_NUMBER function to return only the "best" row per subject:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Student
    ([Name] varchar(1), [Subject] varchar(1), [Marks] int)
;

INSERT INTO Student
    ([Name], [Subject], [Marks])
VALUES
    ('a', 'M', 20),
    ('b', 'M', 25),
    ('c', 'M', 30),
    ('d', 'C', 44),
    ('e', 'C', 45),
    ('f', 'C', 46),
    ('g', 'H', 20)
;

Query 1:

SELECT Name, Subject, Marks
FROM(
  SELECT *, ROW_NUMBER()OVER(PARTITION BY Subject ORDER BY Marks DESC) rn
    FROM dbo.Student
)X
WHERE rn = 1

Results:

| NAME | SUBJECT | MARKS |
--------------------------
|    f |       C |    46 |
|    g |       H |    20 |
|    c |       M |    30 |

Pheon

Actually, it was in my mind. But +1 for the query. I recommend this query. – Geniagenial

Answer 2

You can use other functions and cte also to get the result..

eg : 1

select B.Name,
       A.Subject,
       B.Marks
from ( select Subject,
              max(Marks) as High_Marks
         from Student
       group by Subject
     ) a
  join Student b
    on a.subject = b.subject
   and a.high_Marks = b.Marks

Eg : 2 : use of cte and dense_rank function

;WITH cte

AS

(

SELECT

   [Name],

   [Subject],

   [Marks],

   dense_rank() over(partition BY [Subject] order by [Marks] DESC) AS Rank

FROM Student

)

SELECT * FROM cte WHERE Rank = 1;

Gaucherie

Answer 3

This Basic Query should work for your req.

SELECT Name, Subject, Max(Marks)
FROM Student
GROUP by Subject;

Tried in SQLFiddle

Note: Used SQLite for check

Daredeviltry

In general, this doesn't work. You have not specified that the Name should come from the same row that has the maximum mark. Although this works in SQLite, MySQL gives the wrong Name. Both PosgreSQL and MS SQL Server correctly report the error "Column 'Student.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – Striated

Answer 4

Select * FROM (
SELECT subject, name, marks, ROW_NUMBER() OVER ( PARTITION BY subject ORDER BY marks DESC) As Rankings 
FROM students) T1
WHERE Rankings = 1  ; 

Fizzy

Answer 5

SQL> with cte as
  2  (
  3  select name, subject, marks, dense_rank() over (partition by subject order
by marks desc) rnk
  4  from student)
  5  select name, subject, marks
  6  from cte
  7  where rnk=1;

N S      MARKS
- - ----------
f c         46
c m         30

SQL>

Anglesey

Answer 6

SELECT Max(Name) as Name, Subject, Max(Marks) as Marks
FROM Student
group by Subject

Grounder

this solution won't return Correct name – Sfax

Answer 7

The below query will work perfectly:

select subject_name,student_name from student
inner join mark m using(student_id)
inner join subject su using(subject_id)
inner join (select subject_id,max(value) as maximum from mark m group by subject_id)
highestmark using(subject_id) where highestmark.maximum = m.value
order by subject_name,student_name;

Divaricate

Answer 8

This query will work

select name,subject,marks from stud where marks in (select max(marks) from stud      group by subject) ;

Duo

Answer 9

By using GROUP BY and Having clause that give you simple sql query to find maximum marks in each subject

SELECT Name, Subject, Marks from Student GROUP BY (Subject) HAVING Marks=max(Marks);

Wohlert