Mysql – How to use GROUP BY and result with highest id value

greatest-n-per-groupgroup byMySQL

I used the following query to get the result

SELECT  `subjects`.*,`staffs`.`name`
    FROM  `subjects`
    LEFT JOIN  `staffs`  ON `staffs`.`uid` = `subjects`.`teacher`
    LEFT JOIN  `subject_student_selective`
       ON  `subject_student_selective`.`course` = `subjects`.`classid`
      AND  `subject_student_selective`.`subject` = `subjects`.`subject`
      AND  `subject_student_selective`.`student` = '12947'
    WHERE  `subjects`.`classid` = '26'
      AND  `subjects`.`active` = '1'
      AND  (`subjects`.`type`='normal'
              OR  `subject_student_selective`.`id`>0
           )
    GROUP BY  `subjects`.`subject`
    ORDER BY  `subjects`.`sort_order`

This gave the result but, I need to get the result with highest ID'ed Group By subject.

Best Answer

For the one row with the highest ID:

ORDER BY ID DESC LIMIT 1

For the "latest" information associated with each subject, the problem is considerably more complex. Search for "groupwise max". Here's one link: http://mysql.rjweb.org/doc.php/groupwise_max

Related Solutions

Mysql – How to filter specific entries from a MySql table

This is what you need.

SELECT DISTINCT `original_results`.`Reg.No`
FROM  `results` AS `original_results`
WHERE NOT EXISTS (
    SELECT DISTINCT `helper_results`.`Reg.No`
    FROM `results` AS `helper_results` 
    WHERE `helper_results`.`Reg.No` = `original_results`.`Reg.No` AND `helper_results`.`Pass/Fail` = 'pass'
)

Mysql – How to filter maximum results entries

This is a way of doing it. Can be tested in http://sqlfiddle.com/#!2/7c4525/4

SELECT R1.*
FROM Results R1
INNER JOIN
(
  SELECT RegNo, SubjectCode, Max(Marks) AS MaxMark
  FROM Results
  GROUP BY RegNo, SubjectCode
) R2
ON R1.RegNo = R2.RegNo 
  AND R1.SubjectCode = R2.SubjectCode 
  AND R1.Marks = R2.MaxMark

This previous solution could output several rows for the same Reg.No and SubjectCode in case that student tied for her best result in more than one attempt. In this case, if only the row from the last attempt is wanted to be shown you can use the following variation. Can be tested in http://sqlfiddle.com/#!2/070c5/3. Note that I have added an extra row to the sample data you provided in order to reproduce the situation.

SELECT R1.RegNo, R1.SubjectCode, Max(R1.Attempt), R1.Grade, R1.Marks
FROM Results R1
INNER JOIN
(
  SELECT RegNo, SubjectCode, Max(Marks) AS MaxMark
  FROM Results
  GROUP BY RegNo, SubjectCode
) R2
ON R1.RegNo = R2.RegNo 
  AND R1.SubjectCode = R2.SubjectCode 
  AND R1.Marks = R2.MaxMark
GROUP BY RegNo, SubjectCode, Grade, Marks

However, if in case of ties, the prefered result to be shown is the lower attempt number Max(R1.Attempt) should be replaced by Min(R1.Attempt)

Best Answer

Related Solutions

Mysql – How to filter specific entries from a MySql table

Mysql – How to filter maximum results entries

Related Question