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..
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_equipment
WHERE 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_equipment
WHERE 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 ALL
SELECT * FROM MYTABLE t
WHERE EQUIPMENT = 'MOUSE' and ROWNUM <= 2
UNION ALL
SELECT * FROM MYTABLE t
WHERE EQUIPMENT = 'MONITOR' and ROWNUM <= 2;
try:
select * from MYTABLE t where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2
union
select * from MYTABLE t where EQUIPMENT = 'MOUSE' and ROWNUM <= 2
union
select * from MYTABLE t where EQUIPMENT = 'MONITOR' and ROWNUM <= 2
Try this:
select * from (
select * from MYTABLE t where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2
union
select * from MYTABLE t where EQUIPMENT = 'MOUSE' and ROWNUM <= 2
union
select * from MYTABLE t where EQUIPMENT = 'MONITOR' and ROWNUM <= 2 )