--测试数据作成 with message as ( select 1 as messageid, '001' as userid, to_date('20100101','YYYYMMDD') createdate from dual UNION ALL select 2 as messageid, '001' as userid, to_date('20100201','YYYYMMDD') createdate from dual UNION ALL select 3 as messageid, '001' as userid, to_date('20100111','YYYYMMDD') createdate from dual UNION ALL select 4 as messageid, '002' as userid, to_date('20100301','YYYYMMDD') createdate from dual UNION ALL select 5 as messageid, '002' as userid, to_date('20100401','YYYYMMDD') createdate from dual) --正文 SELECT MAX(MESSAGEID) KEEP (DENSE_RANK FIRST ORDER BY CREATEDATE DESC) AS MESSAGEID, USERID FROM MESSAGE GROUP BY USERID
MAX(MESSAGEID)? 怎么不 是max(jivemessage.creationdate) KEEP (DENSE_RANK FIRST ORDER BY CREATEDATE DESC)是什么意思
with message as (
select 1 as messageid, '001' as userid, to_date('20100101','YYYYMMDD') createdate from dual
UNION ALL
select 2 as messageid, '001' as userid, to_date('20100201','YYYYMMDD') createdate from dual
UNION ALL
select 3 as messageid, '001' as userid, to_date('20100111','YYYYMMDD') createdate from dual
UNION ALL
select 4 as messageid, '002' as userid, to_date('20100301','YYYYMMDD') createdate from dual
UNION ALL
select 5 as messageid, '002' as userid, to_date('20100401','YYYYMMDD') createdate from dual)
--正文
SELECT MAX(MESSAGEID) KEEP (DENSE_RANK FIRST ORDER BY CREATEDATE DESC) AS MESSAGEID,
USERID
FROM MESSAGE
GROUP BY USERID
KEEP (DENSE_RANK FIRST ORDER BY CREATEDATE DESC)是什么意思
MESSAGEID USERID
2 001
5 002
from (select messageid,userid,createdate,row_number() over(partition by userid order by createdate desc ) rn)
where rn=1