CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
Christopher
Christopher

Reputation: 71

Oracle SQL Finding specific column value in data set

I'm asking how to find the value what I want to find.

I have the data set seems like below

(D is the newest time)

END_DATE | START_DATE | YN_DC
------------------------------
    D          C          Y
    C          B          Y
    B          A          N

In this case, I want to find the time value "B", which is the start time that the value of YN_DC changed from "N" to "Y"

When the YN_DC column changed to "Y", It will never be changed to "N" again.

How can I find the value B using SQL?

If I use C or Java, I might find the value as using recursive function or something else, but I have no idea when I try to find that in using SQL.

Upvotes: 0

Views: 197

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use the LEAD window function to get the next row's value of YN_DC based on ascending order of start_date. Check if the current row's YN_DC value is N and the next row's value is Y.

select end_date 
from (select d.*, lead(YN_DC) over(order by start_date) as next_yn_dc
      from dataset d
     ) x
where yn_dc='N' and next_yn_dc='Y'

Upvotes: 1

Related Questions