Find max(marks) from 3 different subject for one id and similarly for the second id and so on

Question

Find max(marks) from 3 different subject for one id and similarly for the second id and so on.

Table name - Student

+---------+---------+-------+
| stud_id | Subj    | Marks |
+---------+---------+-------+
| 1       | ENGLISH | 60    |
+---------+---------+-------+
| 1       | MATHS   | 50    |
+---------+---------+-------+
| 1       | HINDI   | 65    |
+---------+---------+-------+
| 2       | ENGLISH | 70    |
+---------+---------+-------+
| 2       | MATHS   | 20    |
+---------+---------+-------+
| 2       | HINDI   | 57    |
+---------+---------+-------+
| 3       | ENGLISH | 72    |
+---------+---------+-------+
| 3       | MATHS   | 88    |
+---------+---------+-------+
| 3       | HINDI   | 62    |
+---------+---------+-------+

Result should be:

+---------+---------+-------+
| stud_id | Subj    | Marks |
+---------+---------+-------+
| 1       | HINDI   | 65    |
+---------+---------+-------+
| 2       | ENGLISH | 70    |
+---------+---------+-------+
| 3       | MATHS   | 88    |
+---------+---------+-------+

Query:

SELECT STUD_ID, SUBJ, MAX(MARKS) marks FROM STUDENT group by id;

What happens when a student has two or three subjects with the same max mark? — The Impaler, Jun 18 '19 at 14:03
Pretty sure this is a [asked (mysql max value group)](https://stackoverflow.com/search?q=mysql+max+value+group) before as there are multiple methods including using `GROUP_CONCAT()` / nested `SUBSTRING_INDEX()` , `LEFT JOIN` or the general approach a deliverd table `.. INNER JOIN ( SELECT ...)` / subquery in the `IN()` with `MAX()` / `GROUP BY` — Raymond Nijland, Jun 18 '19 at 14:21
if you need to handle "ties" which @TheImpaler is asking about you would be using `ROW_NUMBER()` or simulate those with MySQL user variables if you don't have MySQL 8 .. This question is also pretty much [asked (mysql rank)](https://stackoverflow.com/search?q=mysql+rank) before — Raymond Nijland, Jun 18 '19 at 14:21
There's no `id` column in the `student` table, did you mean `group by stud_id`? — Barmar, Jun 18 '19 at 14:50

Alberto Moro · Answer 1 · 2019-06-18T14:42:46.267

In the case of equality I take both IDs.

If this is not the case, remove GROUP BY and replace GROUP_CONCAT() with DISTINCT s1.Subj

SELECT GROUP_CONCAT(DISTINCT s1.stud_id
                    ORDER BY s1.stud_id ASC SEPARATOR ', '),
       s1.Subj,
       s1.Marks
FROM Student s1
   LEFT JOIN Student s2 ON s1.Marks < s2.Marks
     AND s1.Subj = s2.Subj
WHERE s2.stud_id IS NULL
GROUP BY s1.Subj

You can test Here

With LEFT JOIN the row(s) that actually have the max value will have NULL in the right side. Then, I filter the joined result, showing only the rows where the right side is NULL.

Find max(marks) from 3 different subject for one id and similarly for the second id and so on

1 Answers1