一张表
noteid(主键) notecontent contentid mobile lastmodifytime
1 ... 1 ... 2011-10-08 00:01:23
2 ... 1 ... 2011-10-08 00:02:23
3 ... 1 ... 2011-10-08 00:03:23
4 ... 2 ... 2011-10-08 00:04:23
5 ... 2 ... 2011-10-08 00:05:23
6 ... 2 ... 2011-10-08 00:06:23
7 ... 3 ... 2011-10-08 00:07:23
8 ... 3 ... 2011-10-08 00:08:23
9 ... 3 ... 2011-10-08 00:09:23
10 ... 4 ... 2011-10-08 00:10:23
。。。。
现在要按照这样排序
10 ... 4 ... 2011-10-08 00:10:23
9 ... 3 ... 2011-10-08 00:09:23
6 ... 2 ... 2011-10-08 00:06:23
3 ... 1 ... 2011-10-08 00:03:23
sql该怎么写？即每个contentid中取最后更新的，然后再按时间排序。相同contentid只取一次。。。最新更新的放前面。
有两种方法可以:
第一种:
select a.* from TAB a where a.contentid exists
(select t.contentid,max(t.lastmodifytime) from TAB t where t.contentid = a.contentid group by t.contentid
)order by a.lastmodifytime desc ;
第二种可用函数ROW_NUMBER() OVER(PARTITION BY , 也是常用的
SELECT * FROM TAB A WHERE A.CONTENTID IN
(
SELECT CONTENTID
FROM (
SELECT CONTENTID,
ROW_NUMBER() OVER(PARTITION BY T.contentid ORDER BY T.lastmodifytime) RM
FROM TAB T
)
WHERE RM = 1
)
ORDER BY lastmodifytime DESC;
希望对你有帮助, 要记得评分啊!追问
大哥，你的第一条语句提示：exists操作符无效。
第二种方法提示：T.lastmodifytime标示符无效。 PS:用的oracle
｛
select distinct noteid,contentid,lastmodifytime from tablename
order by contentid desc
｝ t
order by t.lastmodifytime desc本回答被提问者采纳
(select t.contentid,max(t.lastmodifytime) from 一张表 t group by t.contentid
)s
where t.contentid=s.contentid and t.lastmodifytime=s.lastmodifytime
order by t.lastmodifytime desc
) from 表名 group by contentid) b on a.lastmodifytime=b.lastmodifytime
order by a.lastmodifytime desc
试试这个吧，希望能帮到你
