How to fetch first N rows from table based on filter condition sql oracle

Question

I'm trying to retrieve first 500 records for each matching ids. I tried following query, but it retrieves total 500 records., not 2500. Each id has more than 500 records.

Example:

select id from table where id in (1,2,3,4,5) and rownum <= 500

Answer 1

Use ROW_NUMBER analytical function to get a unique row number for each record in a partition and then limit the rows in outer query.

Here is an example from emp Table-

SELECT *
  FROM (SELECT a.*,
               ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename)
                  row_num
          FROM emp a)
 WHERE row_num <= 3;

• In above query, row_number function will return a unique number for rows in particular department.
• Where clause in outer query will filter result-set to max 3 records per department.