-- @Original Author: Fabio Serra
-- @Created: 11/15/2005
-- http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10759/functions122.htm

-- Limit the returned records. 
-- Useful for web pagination. Used HR table for this example


SELECT * FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY employee_id) AS rn,
  emp.*
  FROM employees emp
  WHERE phone_number LIKE '650%'
)
WHERE rn BETWEEN 10 AND 20


SELECT * FROM (
    SELECT emp.*, RANK() OVER (ORDER BY employee_id) AS rn
    FROM employees emp
    WHERE first_name LIKE '%ni%'
)
WHERE rn BETWEEN 1 AND 20