How to join the latest previous record with SQL
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 = P.product_id
                              AND C.cancel_reason_no IN (2, 4)
                              AND C.cancel_dt &lt; P.deal_dt
WHERE  P.deal_reason_no IN (1, 3, 5)
AND    P.deal_dt BETWEEN '2014-04-01 00:00:00' AND '2014-04-11 00:00:00'
AND    NOT EXISTS (
   SELECT 1
   FROM   product_shipping C1
   WHERE  C1.product_id = C.product_id
   AND    C1.cancel_reason_no IN (2, 4)
   AND    C1.cancel_dt &lt; P.deal_dt
   AND    C1.cancel_dt &gt; C.deal_dt
   );


Assuming (product_id,cancel_dt) to be UNIQUE, else you need tiebreaker criteria to be unambiguous. This way you do not need GROUP BY, because only one (or no) row is left-joined.

Also assuming deal_dt and cancel_dt are supposed to come from the same cancelled deal.

This would be simpler with the window function lag(), but your version does not seem to support that according to the discussion under the duplicate post.