title: MySQL - Looking for Top 10 records by Month
body: <p>I will preface with Table Structures:</p>
  
  <p>revshare r : contains info for a purchase including orderNo, sales, commission, itemid, EventDate</p>
  
  <p>Products p: contains information around a product including a PID (Product ID) and is used to join to the Merchants table to get Merchant information.</p>
  
  <p>Merchants m: contains information about the merchant the product was purchased from, including MerchantName</p>
  
  <p><strong>Question</strong></p>
  
  <p>I am trying to create a MySQL query to pull top 10 itemid's ordered by sum of commission for a given month.  The entire data set I would like to get is from 2011-2013 so each year would populate 120 records (10 per month).</p>
  
  <p>I created a query to pull 1 months worth of data and planned on using a UNION ALL to just create a records list with 10 records from each query (each individual query representing a months top 10 itemid's).</p>
  
  <p><strong>Query1</strong></p>
  
  <p>This query accurately returns me the top 10 itemid's based on total commission of those items in the given month period.</p>
  
  <pre><code>SELECT 
  m.MerchantName, 
  Count(r.OrderNo), 
  sum(r.commission)
  
  FROM revshare r
  LEFT JOIN Products p ON r.itemid = p.PID
  LEFT JOIN Merchants m ON p.MID = m.MID
  
  WHERE r.EventDate between '2011-01-01' and '2011-01-31'
  
  GROUP by r.itemid
  ORDER by 3 DESC LIMIT 10
  </code></pre>
  
  <p>When I try to UNION this query with another so that I can get records for the next month between '2011-02-01' and '2011-02-31' I get and error "ERROR: Incorrect usage of UNION and ORDER BY"  I know this is because apparently you cannot use ORDER BY on any set of UNION'd queries but the last.  I could pull the entire data set and then use Excel or Pentaho BI to show only the top 10 but that is not efficient based on the huge data sets in the revshare table. </p>
  
  <p>Below is the query with the UNION ALL that doesn't work.  Does anyone have any better method of pulling this data?</p>
  
  <p>Any help is greatly appreciated.</p>
  
  <p>Regards,
  -Chris</p>
  
  <p><strong>Query 2 (doesn't work because of the ORDER BY statement)</strong></p>
  
  <pre><code>SELECT 
  m.MerchantName, 
  Count(r.OrderNo), 
  sum(r.commission)
  
  FROM revshare r
  LEFT JOIN Products p ON r.itemid = p.PID
  LEFT JOIN Merchants m ON p.MID = m.MID
  
  WHERE r.EventDate between '2011-01-01' and '2011-01-31'
  
  GROUP by r.itemid
  ORDER by 3 DESC LIMIT 10
  
  UNION ALL
  
  SELECT 
  m.MerchantName, 
  Count(r.OrderNo), 
  sum(r.commission)
  
  FROM revshare r
  LEFT JOIN Products p ON r.itemid = p.PID
  LEFT JOIN Merchants m ON p.MID = m.MID
  
  WHERE r.EventDate between '2011-02-01' and '2011-02-31'
  
  GROUP by r.itemid
  ORDER by 3 DESC LIMIT 10
  </code></pre>