get result depending on other column-CodePudding

I'm new in SQL and I'm struggling on this.

So I'd like to only get rows where IsValidated is different from "1" (IsValidated != 1) from the last IsValidated = 1

Select * 
From x
order by date desc

Id	Date	IsValidated
1	2022-09-30	0
2	2022-09-24	0
3	2022-09-23	1
4	2022-09-22	0

expected result is

Id	Date	IsValidated
1	2022-09-30	0
2	2022-09-24	0

Actually, I was thinking of something like

WHERE IsValidated != 1 AND 
-- (that's where how I don't know how to proceed but I was thinking something like) --
 Date > Date of last IsValidated = 1

Can you help me understanding how should I proceed please

CodePudding user response：

select * from T
where Date > (select max(Date) from T where IsValidated = 1)
order by Date desc;

CodePudding user response：

you can try this

with table_1 (ID,DATE,IsValidated)
as
(
          Select '1', '2022-09-30', '0'
Union all Select '2', '2022-09-24', '0'
Union all Select '3', '2022-09-23', '1'
Union all Select '4', '2022-09-22', '0'
)
Select 
    ID,
    Date,
    IsValidated
from
    (
    Select
        *,
        lag (counter) over (order by Date desc) as comparison
    from(
        Select 
            *,
            RANK () OVER ( PARTITION BY IsValidated ORDER BY Date DESC) as counter
        from 
            table_1
        ) x
    )Y
where comparison   1 = counter or comparison is null 
order by Y.Date desc