title: Window function is not supported in order by clauses
body: <p>I am using Amazon Redshift to query the data. I want to select the top 4 products that generated most revenue within each quarter group. I used windows function to calculate the sum value of revenues per product within a quarter then use another window function to pick up the top 4 items in each group. Here is my code:</p>
  
  <pre><code>SELECT  es.itemid,es.total_Revenue,es.quarter
  FROM (         
        SELECT  SALE_CET.itemid
        --get total revenue per quarter
               ,SUM(SALE_CET.totalseller) OVER
                      (PARTITION BY  SALE_CET.itemid,SALE_CET.quarter) AS total_Revenue
               ,SALE_CET.quarter
        --Rank the data by total revenue within each group 
               ,ROW_NUMBER() OVER
                      (PARTITION BY  SALE_CET.quarter ORDER BY total_Revenue DESC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS ra
        FROM SALE_CET) es
  WHERE es.ra &lt;=4
  </code></pre>
  
  <p>I know may be we could not use aggregate value in an order-by clause. But if I want to order the records by its total revenue per item per quarter, how I can implement it? </p>
  
  <p>Thanks for your help.</p>
  
  <p>The SALE_CET table is a tempary table created from CTE. 
  The Create table command will be like:</p>
  
  <pre><code>WITH SALE_CET (userid,itemid, totalseller,year,month,quarter,dayofyear) AS 
      ( SELECT  userid 
               ,itemid
               ,itemprice*orderqty AS TotalSeller
               ,DATEPART (year, orderdatetime) AS year
               ,DATEPART (month, orderdatetime) AS month
               ,DATEPART (quarter, orderdatetime) AS quarter
               ,DATEPART (dayofyear, orderdatetime) AS dayofyear           
        FROM   sales)
  </code></pre>