如何使用具有最高id值的组BY和结果

Question

我使用下面的查询来获得结果

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` 

这给了结果，但，我需要得到的结果与最高ID组的主题。

Answer 1

对于ID最高的一行：

ORDER BY ID DESC LIMIT 1

对于与每个subject相关的“最新”信息，问题要复杂得多。搜索"groupwise max“这里有一个链接：http://mysql.rjweb.org/doc.php/groupwise_最大值