select * from MYTABLE t
where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2 or
EQUIPMENT = 'MOUSE' and ROWNUM <= 2 or
EQUIPMENT = 'MONITOR' and ROWNUM <= 2;
I am trying to run a query that returns matches on a field (ie equipment) and limits the output of each type of equipment to 2 records or less per equipment type.. I know this probably not the best way to use multiple where clauses but i have used this in the past separated by or statements but does not work with rownum. It seems it is only returning the very last where statement. thanks in advance..
try:
Try this:
The Row count can be specific to every Equipment type!
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:
SQLFIDDLE