SQL Oracle rownum on multiple where clauses?

Question

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

MT0 · Accepted Answer · 2014-01-07T21:44:42.587

5

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

edited Jan 07 '14 at 21:44

answered Jan 07 '14 at 21:17

MT0

143,790
11
59
117

Thank You! this works.. how would I also specify for each record in this query you provided that the (status = 'ACTIVE')? just add (and status = 'ACTIVE') beside (WHERE row_num <= 2) thanks again!! EDIT: I would like it to select at most 2 assets that are active instead of it filtering the results after which may include inactive. hope that makes sense.. – emvee Jan 07 '14 at 21:40
Nevermind i got it work on the active assets by putting: WHERE STATUS = 'A' and EQUIPMENT IN ...... – emvee Jan 07 '14 at 21:46
Edit added to pull out only the equipment with an active status. – MT0 Jan 07 '14 at 21:47

Maheswaran Ravisankar · Answer 2 · 2014-01-07T21:22:58.277

4

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;

edited Jan 07 '14 at 21:22

answered Jan 07 '14 at 21:14

Maheswaran Ravisankar

17,652
6
47
69

jcho360 · Answer 3 · 2014-01-07T21:27:59.620

1

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 )

edited Jan 07 '14 at 21:27

answered Jan 07 '14 at 21:15

jcho360

3,724
1
15
24

This does not return two rows per equipment type - it only returns two rows. – MT0 Jan 07 '14 at 21:18
No, if you want at most two rows PER EQUIPMENT TYPE then you do not want to increase `ROWNUM` as you may then get four keyboards and not two keyboards and two mice. – MT0 Jan 07 '14 at 21:23

score 1 · Answer 4 · answered Jan 07 '14 at 21:24

1

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

answered Jan 07 '14 at 21:24

Brian McGinity

5,777
5
36
46

SQL Oracle rownum on multiple where clauses?

4 Answers4

Linked