求一个sql

[yyz0832]

insert into student(name,classid) values
(1,'yuan',1),
(2,'yuanyongzhi',1),
(3,'zhang',2),
(4,'zhangsan',2),
(5,'lisi',3),
(6,'wangwu',4)

想要的结果 即如果classid相同，取ID最大的
+----+-------------+---------+
| id | name | classid |
+----+-------------+---------+
| 6 | wangwu | 4 |
| 5 | lisi | 3 |
| 4 | zhangsan | 2 |
| 2 | yuanyongzhi | 1 |
+----+-------------+---------+

[yyz0832]

插入语句写错了
insert into student(id,name,classid) values
(1,'yuan',1),
(2,'yuanyongzhi',1),
(3,'zhang',2),
(4,'zhangsan',2),
(5,'lisi',3),
(6,'wangwu',4)

[阿_布]

select * from student t where not exists(select 1 from student where classid=t.classid and id>t.id);

[ACMAIN_CHM]

参考下贴中的例子，及N=1时的方法。

[征集]分组取最大N条记录方法征集
http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html

[my_caterpillar]

也想去看看这样的文件哈！谢喽！

[百年树人]

select *
from student s
where not exists(select 1 from student where classid=s.classid and id>s.id)

[zxcvbnmluton]

select max(id) as id,name,classid from tb order by classid desc

[zxcvbnmluton]

上面数据库表名写错了

select max(id) as id,name,classid from student order by classid desc

[wwwwb]

SELECT a.id,a.name,a.classid FROM t2v a
LEFT JOIN t2v b
ON a.classid=b.classid AND a.id <=b.id
GROUP BY a.id,a.name,a.classid
HAVING COUNT(b.id) <=3


SELECT a.id,a.name,a.classid FROM t2v a
WHERE 3>=(
SELECT COUNT(*) FROM t2v b
WHERE a.classid=b.classid AND a.id <=b.id)

[geneshell]

我用的是MySQL 5，SQL如下，已经测试过，没问题，:D

select max(id), name, classid from student group by classid

[vipper23]

mysql> select * from student a where not exists (select 1 from student where classid=
    -> a.classid and id>a.id) order by id desc;
+------+-------------+---------+
| id   | name        | classid |
+------+-------------+---------+
|    6 | wangwu      |       4 |
|    5 | lisi        |       3 |
|    4 | zhangsan    |       2 |
|    2 | yuanyongzhi |       1 |
+------+-------------+---------+