SELECT * 
  FROM product p
/*убираю дубликаты записей с одинаковым status expirydate contractid carnum*/    
 INNER JOIN (SELECT MAX(cd.carid) AS cadid
                    , cd.carnum
                    , cd.expirydate
                    , cd.status
                    , cd.contractid
                    , MAX(cd.product) AS product            
               FROM contracts cd          
              GROUP BY cd.carpan
                       , cd.expirydate
                       , cd.status
                       , cd.contractid
                       ) cd on (cd.contractid = p.contractid)
 INNER JOIN carattr cr ON (cr.objectid = cd.carid)
---------------------------------------------------------------
SELECT * 
  FROM product p
/*убираю дубликаты записей с одинаковым status expirydate contractid*/  
 INNER JOIN (SELECT k.rn
                    , k.carid
                    , k.status
                    , k.product
                    , k.expirydate
                    , k.contractid
               FROM ( SELECT ROW_NUMBER() OVER (partition BY c.carnum, c.status, c.expirydate, c.product ORDER BY c.carid desc) RN
                             , c.carid
                             , c.carnum
                             , c.status
                             , c.product
                             , c.expirydate
                             , c.contractid 
                        FROM cards c ) k
              WHERE k.rn = 1
                       ) cd on (cd.contractid = p.contractid)
 INNER JOIN carattr cr ON (cr.objectid = cd.carid)