CODEWITHSUNDEEP
mysqlsqldate
Andy
Andy

Reputation: 27

Previous row value in MySQL

I'm trying to find a way of retrieving a value from the previous row. What I want to do is first sort the rows by Date 1 (earliest first). Then, if Date 2 is later than all previous dates in that column, I want to pull out that row (plus the first initial row). My server does not support the LAG function. I have tried suggestions using CTE, but my server does not seem to recognise that either.

What I want to do is check whether, after sorting by Date 1, if Date_2 for row 2 > Date_2 for row 1, and if so return that row.

Here's an example table. As you can see, the ID is not in the same order as Date 1. ID Date 1 Date 2 1 2000-01-01 2010-01-01 2 2001-08-01 2013-06-01 3 2000-06-01 2011-01-01 4 1999-07-01 2010-12-01 5 2002-02-01 2012-12-01

So in my example, I want these 3 records to be returned: ID Date_1 Date_2 Previous_max 4 1999-07-01 2010-12-01 NULL 3 2000-06-01 2011-01-01 2010-12-01 2 2001-08-01 2013-06-01 2011-01-01

ID 1 and 5 are not returned because Date 1 is later and Date 2 is earlier than another row (4 and 2 respectively).

Upvotes: 0

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You should be able to do this with a correlated subquery:

  select t.*,
         (select max(date_2) from table t2 where t2.date_1 < t.date_1) as prev_max
  from table t
  having prev_max is null or prev_max < date_2;

Upvotes: 2

Related Questions