title: SQL Insert Statement that pulls top n from each set of categories that could have duplicates
body: <p>I am trying to write an Insert statement that will go through sales numbers for a group of people with each sale being marked as an R or C type of sale. I want to find the TOP 100 salespersons in ALL (both R and C), R, and C. Not only do I have sales data though, I have Sales, Margin, Count, Sales/Count data I want to do the same thing for. so far I have to do <strong>12 SQL statements</strong> to accomplish this (4 categories X 3 sales types) each one is a slight variation of this to get one of my 4 categories.</p>
  
  <pre><code>INSERT INTO ztbl_AllTopSalesPerson (SalesPerson)
  SELECT TOP 100 tbl_Master.SalesPerson
  FROM tbl_Master
  WHERE tbl_Master.SaleType="C"
  GROUP BY tbl_Master.SalesPerson
  ORDER BY Sum(tbl_Master.Margin) DESC;
  
  INSERT INTO ztbl_AllTopSalesPerson (SalesPerson)
  SELECT TOP 100 tbl_Master.SalesPerson
  FROM tbl_Master
  WHERE tbl_Master.SaleType="R"
  GROUP BY tbl_Master.SalesPerson
  ORDER BY Sum(tbl_Master.Margin) DESC;
  
  INSERT INTO ztbl_AllTopSalesPerson (SalesPerson)
  SELECT TOP 100 tbl_Master.SalesPerson
  FROM tbl_Master
  GROUP BY tbl_Master.SalesPerson
  ORDER BY Sum(tbl_Master.Margin) DESC;
  </code></pre>
  
  <p>Ideally I would like a way to make this all <strong>one statement</strong>. And(if it is not impossible) I would like to filter each one by date so I can do it by monthly data too, not just overall. </p>
  
  <p>Just a few notes: I cant have duplicate names, so if a salesperson is top in all three sales types, they still only appear once. Im using Access with a SQL Server back-end for only the main data table. I cant take the top 300 results, because there is so much overlap between the sales types, and I need the top from each ( I do a separate query after this list is made that lines up the SalesPersons' Alphabetically with their 4 categories as fields). And lastly, I generally up with a final list that has around 260-290 records.
  THANKS!
  p.s. thanks for your replies, stack exchange has saved my bacon 100s of times. I would post my attempts at this, but I think it would hurt more than it would help.</p>