Filter on a window function without writing an outer SELECT statement

oraclesubquerywindow functions

Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. Here is the sqlfiddle.

SELECT MERCHANTID, WAREHOUSEID, PRODUCTCODE
FROM (
  SELECT 0
  , WMP.MERCHANTID
  , WMP.WAREHOUSEID
  , WMP.PRODUCTCODE
  , RANK() OVER (PARTITION BY ML.MASTERMERCHANTID, WMP.PRODUCTCODE ORDER BY ML.PREFERENCEORDER ASC NULLS LAST) MERCH_RANK
  , RANK() OVER (PARTITION BY WMP.MERCHANTID, WMP.PRODUCTCODE ORDER BY WM.PREFERENCEORDER ASC NULLS LAST) WARE_RANK
  FROM MW_WAREHOUSEMERCHANTPRODUCT WMP
    LEFT OUTER JOIN MW_WAREHOUSEMERCHANT WM ON 0=0
                AND WMP.MERCHANTID  = WM.MERCHANTID
                AND WMP.WAREHOUSEID = WM.WAREHOUSEID
    LEFT OUTER JOIN MW_MERCHANTLINK ML ON 0=0
                AND WMP.MERCHANTID = ML.LINKEDMERCHANTID
    LEFT OUTER JOIN MW_WAREHOUSEMERCHANTPRODUCT MASTER ON 0=0
                AND ML.MASTERMERCHANTID = MASTER.MERCHANTID
                AND WMP.PRODUCTCODE     = MASTER.PRODUCTCODE
  WHERE 0=0
    AND WMP.STOCKLEVEL > 0
    AND NVL(MASTER.STOCKLEVEL, 0) <= 0
)
WHERE 0=0
  AND MERCH_RANK = 1
  AND WARE_RANK  = 1
;

Best Answer

Yes, most analytic functions can be rewritten with semi-join. In your case it would probably not be very efficient:

SELECT <select_list>
  FROM <table_list>
 WHERE <where_conditions>
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (ML.MASTERMERCHANTID, WMP.PRODUCTCODE) 
                  = (ml_in.MASTERMERCHANTID,wmp_in.PRODUCTCODE)
          )
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (WMP.MERCHANTID, WMP.PRODUCTCODE) 
                  = (wmp_in.MERCHANTID, wmp_in.PRODUCTCODE)
          )
Related Question