My table TEST has three columns:
xID xDATE xVALUE
01134289 19/11/2009 1
01134289 28/12/2009 1
01134289 31/03/2010 0
01134289 10/06/2010 0
01134289 28/10/2010 0
01134289 30/03/2012 1
01134289 14/05/2014 1
01134289 17/07/2014 0
01134289 30/03/2015 1
01134289 14/05/2015 1
01134289 17/07/2015 0
I need help for a query to select the MAX xDATE of rows where xVALUE=1, but only for those rows which previous one row has xVALUE=0.
In this example, there are two rows with xValue=1 and previous rows has xValue=0:
xID xDATE xVALUE
...
01134289 28/10/2010 0
01134289 30/03/2012 1
...
01134289 17/07/2014 0
01134289 30/03/2015 1
The query should output:
xID xDATE xVALUE
01134289 30/03/2015 1
Best Answer
Make use of LAG analytic function
Demo