views:

29

answers:

0

Hello, 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 something 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.