SELECT into with DESC and ROWNUM

Question

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 getting 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

score 5 · Accepted Answer · answered Jul 04 '11 at 12:55

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

score 3 · Answer 2 · answered Jul 04 '11 at 12:54

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

SELECT into with DESC and ROWNUM

2 Answers2