mysql inner join on self, matching previous record

I have a database of transactions, mapped by a parameter called flaglessContractID. Among those, there are cp_flag which may be either 'ca' or 'pu'. I am trying to get in 1 query a result set which contains on a row a transaction price (PRC_O and PRC_C) and also the previous transaction on that contract (flaglessContractID is the same)

I think it would be so开发者_开发问答mething like

select t.PRC_O, t.PRC_C, t.flaglessContractID, t.cp_flag 
from currDataF1 t INNER JOIN 
# here is where it is fuzzy for me. The subquery I want for each row is):
select p.PRC_O, p.PRC_C
from currDataF1
where t.flaglessContractID > p.flaglessContractID)
   AND  t.flaglessContractID = 'ca' 
   and p.flaglessContractID = 'ca'
order by p.flaglessContractID desc limit 1 

Thanks.

If I have understood your question correctly this is what you want:

MYSQL Lag using variables

I have created an example table with a similar structure to yours:

create table currDataF1 (
    id int,
    flaglessContractID int,
    PRC_O decimal(4,2),
    PRC_C decimal(4,2),
    cp_flag varchar(2)
);

insert into currDataF1(id,flaglessContractID,PRC_O,PRC_C,cp_flag)values
(1,1,10.00,10.50,'ca'),
(2,2,20.00,20.50,'ca'),
(3,1,11.00,11.50,'ca'),
(4,1,12.00,12.50,'pu'),
(5,2,21.00,21.50,'ca'),
(6,3,30.00,30.50,'ca'),
(7,4,40.00,40.50,'pu'),
(8,4,41.00,41.50,'pu');

I then use variable to store the previous 'flaglessContractID' details. As you only want to show the details of the previous contract with the same id you must make sure the order of your table is correct (I used the id, but you may have a date?).

It is possible to add a where clause to the SQL that will only show 'ca' or 'pu' rows.

If you need the data in a different order just add an outer select statement.

Here is the query:

select 
    id,
    if(
        (@flaglessContractID_lag:=@flaglessContractID) = (@flaglessContractID:=flaglessContractID),
        if(
            (@cp_flag_lag:=@cp_flag) + (@PRC_O_lag:=@PRC_O) + (@PRC_C_lag:=@PRC_C),
            @flaglessContractID,
            @flaglessContractID),
        if(
            (@PRC_O_lag:=null) + 
            (@PRC_C_lag:=null) +
            (@cp_flag_lag:=null),
            null,
            @flaglessContractID
        )
    ) flaglessContractID,
    (@PRC_O:=PRC_O) PRC_O,
    (@PRC_C:=PRC_C) PRC_C,
    (@cp_flag:=cp_flag) cp_flag,
    @PRC_O_lag PRC_O_lag,
    @PRC_C_lag PRC_C_lag,
    @cp_flag_lag cp_flag_lag
from
    currDataF1 c,
    (select @flaglessContractID:=0) vars
order by c.flaglessContractID , c.id;