0

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     
  • Databases do not keep rows "in order". You must define ORDER BY in order to identify which row is the previous row. – Michael Kutz Oct 26 '18 at 16:05
1

Make use of LAG analytic function

SELECT xID, MAX(xDATE),xValue FROM
(
SELECT t.*,
       CASE
            WHEN xvalue = 1 AND LAG(xvalue) OVER(
                 PARTITION BY xid
                 ORDER BY xdate
            ) = 0 THEN 1
            ELSE 0
       END
  AS cond
FROM test t
) s WHERE s.cond = 1
GROUP BY xID,xValue

Demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.