开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜