SELECT into with DESC and ROWNUM
My current query:
SELECT DACTIONMILLIS, DACTIONDATE INTO WF_DACTIONMILLIS, WF_DACTIONDATE
FROM WORKFLOWHISTORY
WHERE ddocname=? and LOWER(DACTION)=?
and lower(DWFSTEPNAME)=?
and lower(DUSER)=?
and rownum = 1
ORDER BY DACTIONDATE desc;
But because the rownum is applied before the order by I'm g开发者_如何学运维etting invalid results. I found the following topic on stackoverflow: How do I limit the number of rows returned by an Oracle query after ordering? but that discusses a select, not a select into
SELECT DACTIONMILLIS, DACTIONDATE INTO WF_DACTIONMILLIS, WF_DACTIONDATE
FROM (
SELECT DACTIONMILLIS, DACTIONDATE, WF_DACTIONDATE
FROM WORKFLOWHISTORY
WHERE ddocname=? and LOWER(DACTION)=?
and lower(DWFSTEPNAME)=?
and lower(DUSER)=?
ORDER BY DACTIONDATE desc
)
WHERE rownum = 1
The same approach applies as in the question you referenced:
SELECT DACTIONMILLIS, DACTIONDATE
INTO WF_DACTIONMILLIS, WF_DACTIONDATE
from
( SELECT DACTIONMILLIS, DACTIONDATE
FROM WORKFLOWHISTORY
WHERE ddocname=? and LOWER(DACTION)=?
and lower(DWFSTEPNAME)=?
and lower(DUSER)=?
ORDER BY DACTIONDATE desc
)
WHERE rownum = 1
精彩评论