SQL Oracle rownum on multiple where clauses?

WITH numbered_equipment AS (  SELECT t.*,         ROW_NUMBER() OVER( PARTITION BY EQUIPMENT ORDER BY NULL ) AS row_num  FROM   MYTABLE t   WHERE  EQUIPMENT IN ( 'KEYBOARD', 'MOUSE', 'MONITOR' ))SELECT *FROM   numbered_equipmentWHERE  row_num <= 2;

SQLFIDDLE

If you want to prioritize which rows are selected based on other columns then modify the ORDER BY NULL part of the query to put the highest priority elements first in the order.

Edit

To just pull out rows where the equipment matches and the status is active then use:

WITH numbered_equipment AS (  SELECT t.*,         ROW_NUMBER() OVER( PARTITION BY EQUIPMENT ORDER BY NULL ) AS row_num  FROM   MYTABLE t   WHERE  EQUIPMENT IN ( 'KEYBOARD', 'MOUSE', 'MONITOR' )  AND    STATUS = 'Active')SELECT *FROM   numbered_equipmentWHERE  row_num <= 2;

SQLFIDDLE

The Row count can be specific to every Equipment type!

SELECT * FROM MYTABLE t where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2 UNION ALLSELECT * FROM MYTABLE tWHERE EQUIPMENT = 'MOUSE' and ROWNUM <= 2UNION ALLSELECT * FROM MYTABLE tWHERE EQUIPMENT = 'MONITOR' and ROWNUM <= 2; 

try:

select * from MYTABLE t  where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2 unionselect * from MYTABLE t  where EQUIPMENT = 'MOUSE' and ROWNUM <= 2 unionselect * from MYTABLE t  where EQUIPMENT = 'MONITOR' and ROWNUM <= 2