title: Get top 10 products for every category
body: <p>I have a query which is something like this</p>
  
  <pre><code>SELECT 
  t.category, 
  tc.product, 
  tc.sub-product,
   count(*) as sales 
   FROM tg t, ttc tc
   WHERE t.value = tc.value
   GROUP BY t.category, tc.product, tc.sub-product;
  </code></pre>
  
  <p>Now in my query I want to get top 10 products for every category (top by sales ) and for every category I need top 5 sub category (top by sales)</p>
  
  <p>You can assume the problem statement as something like this :</p>
  
  <p>Get top 10 products for each category by sales and for each product get top 5 sub-products by sales .</p>
  
  <ul>
  <li>Here category can be  Books </li>
  <li>Product can be Harry Porter book </li>
  <li>sub productcan be HarryPorter series 5 </li>
  </ul>
  
  <p>Sample input data format </p>
  
  <pre><code>category |product |subproduct |Sales [count (*)]
  
  abc   test1    test11     120
  
  abc   test1    test11     100
  
  abc   test1    test11     10
  
  abc   test1    test11     10
  
  abc   test1    test11     10
  
  abc   test1    test11     10
  
  abc   test1    test12     10
  
  abc   test1    test13     8
  
  abc   test1    test14     6
  
  abc   test1    test15     5
  
  abc   test2    test21     80
  
  abc   test2    test22     60
  
  abc   test3    test31     50
  
  abc   test3    test32     40
  
  abc   test4    test41     30
  
  abc   test4    test42     20
  
  abc   test5    test51     10
  
  abc   test5    test52     5 
  
  abc   test6    test61     5 
  
  |
  
  |
  
  |
  
  bcd   test2    test22     10 
  
  xyz   test3    test31     5 
  
  xyz   test3    test32     3 
  
  xyz   test4    test41     2
  </code></pre>
  
  <p>Output would be "</p>
  
  <pre><code>top 5 rf for (abc) -&gt; abc,test1(289) abc,test2 (140), abc test3 (90), abc test4(50) , abc test5 (15)
  
  top 5 rfm for (abc,test1) -&gt; test11(260),test12(10),test13(8),test14(6),test15(5) and so on
  </code></pre>
  
  <p>My query is failing because results are really huge . I am reading about oracle analytic functions like rank. Can someone help me modifying this query using analytical functions. Any other approach can also work.</p>
  
  <p>I am referring to this <a href="http://www.orafaq.com/node/55" rel="nofollow">http://www.orafaq.com/node/55</a>. But unable to get a right sql query for this.</p>
  
  <p>Any help would be appreciated..I am like stuck for 2 days on this :(</p>