how to get the marks in ‘Math’ of ‘English’ topper for each class in mysql for student result data I need to find the marks in ‘Math’, of ‘English’ topper for each class in MySQL for student result data.

Output schema: class, roll_no, math_marks 

(Here expected result image not exact answer)

 

my table is as,

CREATE TABLE resultdata 
(
  class TINYINT UNSIGNED NOT NULL,
  roll_number TINYINT UNSIGNED NOT NULL,
  subjects VARCHAR (15) NOT NULL,
  marks TINYINT UNSIGNED NOT NULL
);


Populate it:

INSERT INTO resultdata(class,roll_number,subjects,marks) VALUES
(8, 1, 'math', 98),
(8, 1,'english', 88),
(8, 1,'science', 96),
(8, 1,'computer', 94),
(8, 2,'math', 78),
(8, 2,'english', 89),
(8, 2,'science', 87),
(8, 2,'computer', 99),
(9, 1,'math', 90),
(9, 1,'english', 85),
(9, 1,'science', 85),
(9, 1,'computer', 85),
(9, 2,'math', 95),
(9, 2,'english', 30),
(9, 2,'science', 40),
(9, 2,'computer', 80),
(10, 1,'math', 85),
(10, 1,'english', 90),
(10, 1,'science', 65),
(10, 1,'computer', 45),
(10, 2,'math', 50),
(10, 2,'english', 95),
(10, 2,'science', 65),
(10, 2,'computer', 85),
(10, 3,'math', 51),
(10, 3,'english', 61),
(10, 3,'science', 71),
(10, 3,'computer', 81);
This question intrigued me - the requirement to have the mark for Mathematics for the class (regardless of role) which (who?) obtained the best mark in English.

I decided to go one better and obtain the results for all subjects for the best in class in English - fiddle available here. 

The SQL:

SELECT t1.class, t1.roll_number,
       t1.sci_mark,
       t1.comp_mark,
       t1.math_mark,
       t1.eng_mark
FROM 
(
  SELECT 
    class, 
    roll_number,
    SUM(CASE WHEN subjects = 'math'
      THEN marks ELSE 0 END) AS math_mark,
    SUM(CASE WHEN subjects = 'english'
      THEN marks ELSE 0 END) AS eng_mark,
    SUM(CASE WHEN subjects = 'science'
      THEN marks ELSE 0 END) AS sci_mark,
    SUM(CASE WHEN subjects = 'computer'
      THEN marks ELSE 0 END) AS comp_mark
  FROM resultdata
  GROUP BY class, roll_number
) AS t1
JOIN 
(
  SELECT class, MAX(marks) AS maxmark
  FROM resultdata
  WHERE subjects = 'english'
  GROUP BY class
) AS t2
ON 
  t1.class = t2.class AND 
  t1.eng_mark = t2.maxmark
ORDER BY class, eng_mark DESC;


Result:

class   roll_number sci_mark    comp_mark   math_mark   eng_mark
    8             2       87           99          78         89
    9             1       85           85          90         85
   10             2       65           85          50         95


A glance at the other solution here shows that it matches on the Mathematics score for the highest English mark. Inspection shows that it works for the two other subjects (Science and Computers) also. As pointed out here MySQL does not have a PIVOT function, but I got this technique from that site.