If two candidates is having max number of marks how could I grab both details in MySQL

Question

students 

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

Result Should be

Name  |  Subject  | Max(Marks)
 --------------------------
 c        M          30 
 d        M          30
 f        C          46 
 g        H          40
 h        H          40

I have tried

SELECT Name
     , Subject
     , MAX(Marks) 
  FROM students 
 GROUP 
    BY subject.

Name  |  Subject  | MAX(Marks)
--------------------------
c        M          30
f        C          46 
g        H          40

It's showing only first MAX details not other. please help me out on this query.

Answer 1

First you need to find the max marks for the group, then return all rows that have the same mark.

You could do this via a window query, cte, nested select, or a correlated sub query... in this case we'll use a sub query as the syntax should work across many rdbms:

SELECT students.Name, students.Subject, students.Marks
FROM students
INNER JOIN (
    SELECT s.Subject, MAX(s.Marks) AS Marks
    FROM students s
    GROUP BY s.Subject
) AS MaxMarks m ON m.Subject = students.Subject
WHERE students.Marks = m.Marks

Answer 2

This would typically be approached using window functions, in particular rank() or dense_rank():

SELECT s.*
FROM (SELECT Name, Subject, MAX(Marks) as max_marks,
             RANK() OVER (PARTITION BY Subject ORDER BY MAX(Marks) DESC) as seqnum
      FROM students 
      GROUP BY subject
     ) s
WHERE seqnum = 1;