Tag Info

New answers tagged

1

Assuming you have a recent enough version, infomix supports the LAG and LEAD functions over ranking operations, see http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm These will only work if the current row and the one you consider the previous row are find using the same filtering clauses though, otherwise you and ...


1

I suggest you join to previous products that match criteria and (if any exist) narrow down to the one where no later row exists: SELECT P.product_id AS product_id, ,P.deal_dt AS deal_dt ,C.deal_dt AS previous_deal_dt ,C.cancel_dt AS previous_cancel_dt FROM product_shipping p LEFT JOIN product_shipping C ON C.product_id = ...


0

I'm not sure how to join to get latest previous record. Start there. Get the latest previous record with a self-join: select c.product_id, min(p.deal_dt) as prev_dt from product_shipping as c join product_shipping as p on c.product_id = p.product_id and c.deal_dt >= p.deal_dt group by c.product_id That will produce rows for which prev_dt = ...



Top 50 recent answers are included