mysql 查询每个分类id的第一条数据 应该怎么写

比如：
分类表里有分类c1，c2，c3
id name
1 c1
2 c2
3 c3
4 c4

数据表有字段id，分类cid，title，content

id cid title content
1 1 第一条 内容
2 1 第二条 内容2
3 2 第三条 内容3
4 3 第四条 内容4
5 2 第五条 内容5
6 1 第六条 内容6

那么，我如何用一句sql查询列出c1，c2，c3的最新一条数据

如果我用group by cid 显示的是每个分类的早一条记录
SELECT * FROM topic where cid in(1,2,3) group by cid order by id desc

先按id 倒序排序下再group by 就可以了
select * from (SELECT * FROM topic where cid in(1,2,3) order by id desc ) T group by T.cid

大哥，你怎么那么执着不加一个时间

SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY cid ORDER BY id DESC) m,
student.*
FROM student) n
where n.m = 1

SELECT c.cid,(select id from
(select a.id from topic a where a.cid=c.cid order by id desc ) b where rownum<=1) as id FROM topic c where cid in(1,2,3) group by cid;

留个言， 看看哪个位大神解决 ， 理论上 order by 是最后执行的 也就是group by 已经分组好了 order by 才进行排序 所以你这种方法肯定不行

SELECT max(id),cid,title,content FROM topic where cid in(1,2,3) group by cid;
结果是先确定cid 是1,2,3,再按照cid分组,找出每组最大id和相关字段.

SELECT
a.*
FROM
topic a,
(
SELECT
MAX(id) AS id
FROM
topic
GROUP BY
cid DESC
) b
WHERE
a.id = b.id 我测试可以用

先按id 倒序排序下再group by 就可以了（引用后面发的那条问题的采纳答案加以修正）
select * from (SELECT * FROM topic where cid in(1,2,3) order by id desc limit 0,10000) T group by T.cid

原因：limit 0,10000可以让子查询先查询。而之前的语句也是group by 先于 order by执行，加limit 0,10000则可先order by 在group by 即先排序后分组，这应该才是正确的写法。

select * from (select *,ROW_NUMBER() over(partition by cid order by id desc) as rn from topic)x where rn =1