I have two tables, which I want to joinTable Deals* id* date_date* ccy_pair* amount* rate* buyOrSell* order_idTable Orders* order_id* status* sent_date* ccy_pair* buyOrSell* rate* amountFor orders that have no deals i.e order with a 'Status' of 'CANCELED', I want to find the next deal (i.e the deal that came after the order sent date) for the same ccy_pair and buyOrSell column values.select deals.deal_date as d_date, orders.order_id ,orders.buysell, orders.ccy_pair, orders.amount as o_amount, orders.rate as o_rate, deals.amount as d_amount, deals.rate as d_ratefrom orders inner join deals onand orders.status = 'CANCELED'--trying to find deal and deals.id = (SELECT id FROM (SELECT id, rank() over (partition by ccy_pair,buysell order by deal_date) rnk FROM deals where ccy_pair= orders.ccy_pair and deal_date > orders.sent_date and buysell = orders.buysell ) WHERE rnk = 1)order by orders.sent_date desc
This is my beat attempt but I keep getting exceptions. Any help please?