MySQL：求助个基础的SQL问题

有表school如下：

CREATE TABLE school (
    class INT(11),
    name VARCHAR(20),
    status INT(11)
);

插入测试数据

INSERT INTO school VALUES(1, 'Tim', 2);
INSERT INTO school VALUES(1, 'Jack', 3);
INSERT INTO school VALUES(2, 'Lucy', 1);
INSERT INTO school VALUES(2, 'Tom', 3);
INSERT INTO school VALUES(2, 'Lily', 2);

需按class分组，并获取每班status最大的那条记录，最终得到

class name status
1     Jack 3
2     Tom  3

求解，谢谢

---

用子查询:
SELECT * FROM (SELECT class,name,status from school ORDER BY status DESC) as result GROUP BY result.class

---

SQL：SELECT id,MAX(status) status from demo GROUP BY id

---

select school.class, school.name, school.status from school inner join (SELECT class,MAX(status) as status from school GROUP BY class) B on school.class = B.class and school.status = B.status;

+-------+------+--------+
| class | name | status |
+-------+------+--------+
|     1 | Jack |      3 |
|     2 | Tom  |      3 |
+-------+------+--------+