get result depending on other column

DimitriB 0
Viewed 31

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

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

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
user199506270
Related