title: oracle sql select/set rownumber limitation differently for different group in one data set
body: <p>I have a table have 4 columns: customer, product_id, score, tag
  there are 2 value in tag column 'new' and 'old'</p>
  
  <p>for each customer has both product_id from 'new' and 'old' tag(number varies)
  for each customer, we rank product_id based on score and tag
  we have total limitation for the number of product for each customer. called it 'n_prod'</p>
  
  <p>I hope to select (2/3 *n_prod) product_id from 'old' tag and
  (1/3*n_prod) product for 'new' tag for each customer
  e.g if we need to choose 6 product, hope having 4 from 'old' tag (top 4 based on the score), and have 2 from 'new' tag (the top 2 based on score)</p>
  
  <p>I am able to create a column called 'rn' use the following command to rank product_id based on each customer and tag</p>
  
  <pre><code>select customer, product_id, score, tag
        , row_number()over(partition by customer,tag, order by score desc) as rn 
  from table
  </code></pre>
  
  <p>the number of limitation is different for different group: tried this but does not work:</p>
  
  <pre><code>with tep as
  (select customer, product_id, score, tag
        , row_number()over(partition by customer,tag, order by score desc) as rn 
  from table)
  
  select tep.*
  from tep
  where ( case 
           when tag='new' then rn&lt;= round(n_prod*0.33,0)
           else then rn&lt;= round(n_prod*0.66,0)
           end
  );
  
  </code></pre>
  
  <p>could I combine 'where' with 'case when ' or 'if else'?</p>
  
  <p>restate the expected result:
  I hope to select (2/3 *n_prod) product_id from 'old' tag and
  (1/3*n_prod) product for 'new' tag for each customer
  e.g if we need to choose 6 product, hope having 4 from 'old' tag (top 4 based on the score), and have 2 from 'new' tag (the top 2 based on score)</p>