Filter on a window function without writing an outer SELECT statement
Yes, most analytic functions can be rewritten with semi-join. In your case it would probably not be very efficient:

SELECT &lt;select_list&gt;
  FROM &lt;table_list&gt;
 WHERE &lt;where_conditions&gt;
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM &lt;table_list&gt; alias_in
           WHERE &lt;where_conditions&gt;
             AND (ML.MASTERMERCHANTID, WMP.PRODUCTCODE) 
                  = (ml_in.MASTERMERCHANTID,wmp_in.PRODUCTCODE)
          )
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM &lt;table_list&gt; alias_in
           WHERE &lt;where_conditions&gt;
             AND (WMP.MERCHANTID, WMP.PRODUCTCODE) 
                  = (wmp_in.MERCHANTID, wmp_in.PRODUCTCODE)
          )