CODEWITHSUNDEEP
mysqlsql
Bascy
Bascy

Reputation: 2089

Join a table with previous record of same table

I have a table with historic records, posted there by several triggers in a main table. I want to create a select statement on the history table where I have each record JOINed by their prior record (identified by the same LineID and the highest ActionDate) so I can extract the differences between those two.

I tried this, but (My)SQL does not allow references to the first "FROM" table in the JOINED subselect: Unknown column h1.LineID in where clause

select 
  h1.*, 
  prev.* 
from history h1
LEFT OUTER JOIN 
  (Select * 
     From history h2 
     where  h1.LineID=h2.LineID and h2.ActionDate < h1.ActionDate 
     order by Actiondate desc limit 1
  ) prev on h1.LineID=prev.LineID

How can I accomplish this?

Upvotes: 4

Views: 5040

Answers (3)

Bascy
Bascy

Reputation: 2089

Solved it myself with the following code:

select * 
from history h1
LEFT OUTER JOIN history h2 on h1.LineID=h2.LineID 
    and h2.ActionDate = (select max(actiondate) 
                         from history h3 
                         where h3.LineID=h1.LineId and h3.Actiondate < h1.ActionDate
                        )
order by h1.actiondate desc;

Upvotes: 1

sume
sume

Reputation: 21

Please use below code

select * from history as h inner join history as pre on pre.ActionDate < h.ActionDate and prev.LineID = h.LineID

Hope it will help you.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270663

You can get a reference to the previous row using:

select h.*,
       (select h2.ActionDate
        from history h2
        where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
        order by h2.ActionDate desc
        limit 1
       ) as prev_ActionDate
from history h;

If you want the complete row, you can use a join to get the data:

select h.*, hprev.*
from (select h.*,
             (select h2.ActionDate
              from history h2
              where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
              order by h2.ActionDate desc
              limit 1
             ) as prev_ActionDate
      from history h
     ) h left join
     history hprev
     on hprev.LineId = h.LineId and hprev.ActionDate = h.prev_ActionDate;

Upvotes: 9

Related Questions