I have this result from a query

I want to have only stud who have col2 < 01/04/2001 or those who have col2 < 01/04/2001 and col2 >= 01/04/2001 but not those who have only col2 > 01/04/2001 So I want to have only these rows

any idea please? Thanks in advance

CodePudding user response：

I would recommend exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.col1 = t.col1 and
                    t2.col2 < date '2001-04-01'
             );

You can also phrase this using window functions:

select t.*
from (select t.*,
             min(col2) over (partition by col1) as min_col2
      from t
     ) t
where min_col2 < date '2001-04-01';

CodePudding user response：

To meet your condition, you could follow this below rule : First (in the Subquery), select all the rows which have col2 value less than 01/04/2001, then select all the rows that have same col1 value as for rows filtered in the subquery.

select t1.*
from YourTable t1
where t1.col1 in (
    select t2.col1 from YourTable t2 
    where t2.col2 < to_date('01/04/2001', 'DD/MM/YYYY')
    )
order by col3
;

db<>fiddle