Htykuut

How do I exclude certain rows?
For example, I have the following table:

+------+------+------+

| Col1 | Col2 | Col3 |

+------+------+------+

|    1 | 1    |    R |

|    1 | 2    |    D |

|    2 | 3    |    R |

|    2 | 4    |    R |

|    3 | 5    |    R |

|    4 | 6    |    D |

+------+------+------+

I need to select only:

|    2 | 3    |    R |  

|    2 | 4    |    R |  

|    3 | 5    |    R |  

My select that does not work properly:

with t (c1,c2,c3) as(

select 1 , 1 , 'R' from dual union all

select 1 , 2 , 'D' from dual union all

select 2 , 3 , 'R' from dual union all

select 2 , 4 , 'R' from dual union all

select 3 , 5 , 'R' from dual union all

select 4 , 6 , 'D' from dual), 

tt as (select t.*,count(*) over (partition by c1) cc from t ) select * from tt where cc=1 and c3='R';

Thanks in advance!

select * from table where col2 = 'R'

or if you want to exclude rows with D value just

select * from table where col2 != 'D'

It depends on your requirements but you can do in this way:

SELECT * FROM `table` WHERE col1 = 2 AND col3 = "R"

if you want to exclude just do it like WHERE col1 != 1

You ca also use IN clause also e.g.

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1, value2, ...);

This syntax is for MySql, but you can modify it as per your requirement or database you are using.

this will work :

select * from (select * from table_name) where rownum<=4

minus 

select * from ( select * from table_name) where rownum<=2

My guess is that you want all rows for a col1 where no row for a col1 = D and at least 1 row for a col1 = R. @ where [not] exists may do

DROP TABLE T;

CREATE TABLE T

(Col1 NUMBER, Col2 NUMBER, Col3 VARCHAR(1));

INSERT INTO T VALUES (    1 , 1    ,    'R');

INSERT INTO T VALUES (       1 , 2    ,    'D'); 

INSERT INTO T VALUES (       2 , 3    ,    'R'); 

INSERT INTO T VALUES (       2 , 4    ,    'R'); 

INSERT INTO T VALUES (       3 , 5    ,    'R');

INSERT INTO T VALUES (       3 , 6    ,    'D');

INSERT INTO T VALUES (       4 , 5    ,    'X');

INSERT INTO T VALUES (       4 , 6    ,    'Y');

INSERT INTO T VALUES (       5 , 6    ,    'X');

INSERT INTO T VALUES (       5 , 5    ,    'R');

INSERT INTO T VALUES (       5 , 6    ,    'Y');



SELECT * 

FROM T

WHERE NOT EXISTS(SELECT 1 FROM T T1 WHERE T1.COL1 = T.COL1 AND COL3 = 'D') AND

      EXISTS(SELECT 1 FROM T T1 WHERE T1.COL1 = T.COL1 AND COL3 = 'R');

Result

      COL1       COL2 COL3

---------- ---------- ----

         5          6 X    

         5          5 R    

         5          6 Y    

         2          3 R    

         2          4 R   

use row_number() window function

    with t (c1,c2,c3) as(

select 1 , 1 , 'R' from dual union all

select 1 , 2 , 'D' from dual union all

select 2 , 3 , 'R' from dual union all

select 2 , 4 , 'R' from dual union all

select 3 , 5 , 'R' from dual union all

select 4 , 6 , 'D' from dual

    ),

  t1 as

  (

      select c1,c2,c3,row_number() over(order by c2) rn from t 

  ) select * from t1  where t1.rn>=3 and t1.rn<=5

demo link

C1  C2  C3

2   3   R

2   4   R

3   5   R