Geek Answers Handbook

SQL rownum in several cases?

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 in a field (i.e. equipment) and limits the output of each type of equipment to 2 records or less per type of equipment. I know that this is probably not the best way to use a few where clauses, but I have used this in the past, split or statements, but does not work with rownum. It seems to return only the last statement. thanks in advance.

+4
source share
4 answers
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 change the query part ORDER BY NULLto place the highest priority items in order.

Edit

, , , :

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

+5

!

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;
+4

Try the following:

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 )
+1
source

to 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
+1
source

More articles:


All Articles