SQL Discard previous records from a query when condition is met

Question

Please, I need to discard the records before when the condition is met and keep only those equal to and after the date that condition is met.

Likewise, if the condition is not met in all the records of a type, in the example the "x1" ones, we also need all its records.

It goes inside a stored procedure using Sybase IQ.

Example...

Name	Date	Flag	Action (not a column)
p1	1/2021	n	discard
p1	2/2021	n	discard
p1	3/2021	y	needed, discard previous records (order by date desc) of P1 when condition is met (flag = y)
p1	4/2021	n	needed
p1	5/2021	n	needed
c1	1/2021	n	discard
c1	5/2021	y	needed, discard previous records (order by date desc) of C1 when condition is met (flag = y)
c1	9/2021	n	needed
x1	1/2021	n	needed
x1	2/2021	n	needed
x1	3/2021	n	needed
x1	4/2021	n	needed

Thank you in advance

Answer 1

If I understand correctly, you want all records from 'y' onwards, or all records if there is no 'y'. One method is:

select t.*
from t
where t.date >= (select t2.date
                 from t t2
                 where t2.name = t.name and t2.flag = 'y'
                ) or
      not exists (select 1
                  from t t2
                  where t2.name = t.name and t2.flag = 'y'
                 ) ;

You can actually also express this using all (or any):

select t.*
from t
where t.date >= all (select t2.date
                     from t t2
                     where t2.name = t.name and t2.flag = 'y'
                    ) ;

However, it is not always obvious that this returns all rows if the subquery returns no rows.