如下表:tab 中有如下记录(cid是栏目分类) ID CID title times
1 2 aaa 2009-01-19 21:08:22
2 2 aaa 2009-01-18 01:04:32
3 3 a4a 2009-01-19 21:08:52
4 3 cca 2009-01-17 11:08:13
5 3 aay 2009-01-19 21:08:22
6 2 bbb 2009-01-16 11:28:25
7 2 vva 2010-09-19 56:35:11
8 1 78a 2010-01-18 16:21:33
9 1 lkd 2009-01-19 21:08:22我目前的SQL语句:
SELECT id,cid,max(times) as dtimes,title FROM tab group by cid order by dtimes desc这个SQL语句虽然过滤掉了重复记录信息,显示的结果里,时间一项也显示的是最新时间记录,但是title显示的信息,确是最早添加的信息,请问如何,显示最新的title信息呢?
1 2 aaa 2009-01-19 21:08:22
2 2 aaa 2009-01-18 01:04:32
3 3 a4a 2009-01-19 21:08:52
4 3 cca 2009-01-17 11:08:13
5 3 aay 2009-01-19 21:08:22
6 2 bbb 2009-01-16 11:28:25
7 2 vva 2010-09-19 56:35:11
8 1 78a 2010-01-18 16:21:33
9 1 lkd 2009-01-19 21:08:22我目前的SQL语句:
SELECT id,cid,max(times) as dtimes,title FROM tab group by cid order by dtimes desc这个SQL语句虽然过滤掉了重复记录信息,显示的结果里,时间一项也显示的是最新时间记录,但是title显示的信息,确是最早添加的信息,请问如何,显示最新的title信息呢?
from tab a
where not exists (select 1 from tab where cid=a.cid and times>a.times)
where
not exists
(
select 1 from tab where cid=a.cid and times>a.times
)
(SELECT cid,max(times) as dtimes,title FROM tab group by cid order by dtimes desc) b
on a.cid=b.cid and a.times=b.dtimes
(select cid,max(times) as mt from tab group by cid) t2
on t1.cid=t2.cid and t1.times=t2.mt
order by t1.times desc
sELECT max(time) from table t where t.cid=table.cid group by cid);
on a.cid=b.cid and a.times=b.times;