Oracle: How to return a partial result only?

Question

Im using an Oracle database.

In my query 100 rows are fetched. If I want to filter rows between rownum 50 and 60, what would be the query?

SELECT EMPLID, EFFDT, ACTION, ACTION_REASON 
from JOB where emplid ='12345'

Answer 1

It's a little tricky in Oracle, I think you have to do something like this:

SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
FROM (SELECT ROWNUM rnum, EMPLID,EFFDT,ACTION,ACTION_REASON
      FROM (SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
            FROM JOB
            WHERE emplid = '12345')
      WHERE rownum <= 60
)
WHERE rnum >= 50;

Answer 2

You can use ROWNUM (see this link). If you need to control sort order, you can wrap another query around your query.

Answer 3

Most people will commonly tell you to use ROWNUM, to do this, however the more succinct way is the use the row_number() analytic function.

select EMPLID, EFFDT, ACTION, ACTION_REASON
from
(
SELECT EMPLID, EFFDT, ACTION, ACTION_REASON, row_number() over (order by emplid) rn
from JOB where emplid ='12345'
)
where rn between 50 and 60;

Using the row_number function allows you to order the results AND number them in a single query, then you just need one wrapper query to get the rows you need.