ORACLE sql statement rewrite

Question

SELECT orderid 
 FROM (SELECT orderid, rownum r 
         FROM (SELECT orderid 
                 FROM myorders 
                WHERE ordertype = 'E' 
                  AND orderstatus = 'A') a 
         WHERE rownum < 86) 
 WHERE r > 84

What is the best way to rewrite the ABOVE sql statement in more clear way...

I have tried the following but I am not getting any results.

select orderid 
  from myorders 
 where rownum between 84 and 86

there is no point in taking rows 84 to 86 if you don't apply any sort on the table.... what are you trying to achieve here ? — haki, Feb 21 '13 at 17:15
what about ordertype and orderstatus in your second solution? — Sleiman Jneidi, Feb 21 '13 at 17:16
I don't even see how your first query will compile because you are selecting rownum from a subquery that doesn't contain it. — Dan Bracuk, Feb 21 '13 at 17:17
@DanBracuk - `rownum` is a pseudo-column so this will work. It's only explicitly listed in the second level of query so it can be aliased and the outer query can filter on the same value, rather than the independent `rownum` that applies to that subset of rows. — Alex Poole, Feb 21 '13 at 17:21

score 3 · Answer 1 · answered Feb 21 '13 at 17:23

Assuming that you are trying to generate pages of data and assuming that your desire is that the results are stable and consistent if the data in the table isn't changing (every row from the inner query is returned on exactly one page of the results as you vary the upper and lower bounds), the most efficient approach would be essentially what you posted initially. But you do need to add an ORDER BY to the inner query. Otherwise, it would be perfectly correct for Oracle to return one row of data on every page or to never return a row on any page

SELECT orderid 
 FROM (SELECT orderid, rownum r 
         FROM (SELECT orderid 
                 FROM myorders 
                WHERE ordertype = 'E' 
                  AND orderstatus = 'A'
                ORDER BY <<something>>) a 
         WHERE rownum < 86) 
 WHERE r > 84

If you are really more concerned about readability than performance, you can reduce that by one level of nesting by doing something like

SELECT orderid
  FROM (SELECT orderid, 
               rank() over (order by <<something>>) rnk
          FROM myorders
         WHERE ordertype = 'E'
           AND orderstatus = 'A')
 WHERE rnk > 84
   AND rnk < 86

In Oracle 12c, Oracle is expected to support the ANSI FETCH and OFFSET keywords to simplify the syntax a bit further.

score 2 · Accepted Answer · answered Feb 21 '13 at 17:16

2

Indenting is the first suggestion. But you can eliminate one set of subqueries:

 select orderid
 from (SELECT orderid, rownum r
        FROM myorders
        WHERE ordertype = 'E' AND orderstatus = 'A'
       ) a
 where r = 85

answered Feb 21 '13 at 17:16

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Feb 21 '13 at 17:37

0

If you are dealing with a single table.

SELECT orderid
FROM   myorders 
WHERE  ordertype = 'E' 
AND    orderstatus = 'A'
AND    rownum between 84 and 86

answered Feb 21 '13 at 17:37

JParadiso

49
2

This will never return any values because of the way the `rownum` pseudo-column is generated. This is explained [in an answer to an old question](http://stackoverflow.com/a/855446/266304). – Alex Poole Feb 21 '13 at 17:47
@JParadiso - I agree with Alex Poole - this will never return anything because of nature of ROWNUM. You should test your examples. Here's the test: SELECT empno FROM scott.emp WHERE rownum between 5 and 10 – Art Feb 21 '13 at 20:18

score 0 · Answer 4 · answered Feb 21 '13 at 20:14

In addition general example of selecting between rows. In my personal opinion it is better and wiser to use ROW_NUMBER() not ROWNUM. They are compl. different...:

-- Between rows --
SELECT * FROM 
     ( SELECT deptno, ename, sal
            , ROW_NUMBER() OVER (ORDER BY ename) row_seq 
  FROM scott.emp)
 WHERE row_seq BETWEEN 5 and 10
 /

ORACLE sql statement rewrite

4 Answers4