Mysql ，请大佬帮忙解决一下问题，救救孩子吧

Question

这样的一个table，怎么查出不同城市number最大的cl？？

最终效果：

Answer 1

select t1.* from (select city,max(number) mnum from city group by city ) t
left join (select * from city) t1 on t.city=t1.city and t.mnum = t1.number order by t1.city;

表结构及数据如下：
CREATE TABLE city (
city varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
c1 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
number int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

-- Records of city

---

INSERT INTO city VALUES ('北京', 'T', 20);
INSERT INTO city VALUES ('北京', 'c', 50);
INSERT INTO city VALUES ('深圳', 'T', 50);
INSERT INTO city VALUES ('深圳', 'c', 20);
INSERT INTO city VALUES ('北京', 'T', 50);
INSERT INTO city VALUES ('上海', 'T', 20);

SET FOREIGN_KEY_CHECKS = 1;