Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
;
share|improve this question
2  
No. Only with derived tables or CTEs or correlated subqueries. –  ypercube May 23 '13 at 15:14
2  
What is the motivation for the question? –  Martin Smith May 23 '13 at 15:16
    
@MartinSmith Just interested in whether or not there is a clever way to get around the ambiguity of including a window function in a WHERE clause –  ajw0100 May 23 '13 at 15:24
    
You always need a subquery of some sort because the window function operates on the complete set of returned rows after all predicates and joins etc have been applied. –  David Aldridge May 23 '13 at 16:08

1 Answer 1

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)
          )
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.