Bascy Bascy - 2 months ago 6
MySQL Question

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?

Answer

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;
Comments