SQL Oracle rownum on multiple where clauses?

2019-06-24 02:53发布

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..

4条回答
一夜七次
2楼-- · 2019-06-24 03:04

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 
查看更多
Fickle 薄情
3楼-- · 2019-06-24 03:08

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 )
查看更多
爷的心禁止访问
4楼-- · 2019-06-24 03:16

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; 
查看更多
来,给爷笑一个
5楼-- · 2019-06-24 03:30
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

查看更多
登录 后发表回答