Oracle - query by two column combination

Question

I have two tables:

    product_table:    
    ------------------------------------
     product | market | category | other
    ------------------------------------
             |        |          |
    ------------------------------------


    availability_table:
    -------------------------------------
     product | num_available | some_other
    -------------------------------------
             |               |
    -------------------------------------

I have a list of 7 markets and a list of 41 categories. For each market/category combination, I need to get several products (3 is a good number) that are currently available. I came up with a query that delivers products for one market/category combination:

SELECT product_table.product, product_table.category
FROM product_table, availability_table
WHERE   product_table.market = 1 // market is changing from query to query
    AND product_table.product = availability_table.product
    AND availability_table.num_available > 0
    AND product_table.category = 'category_1' // category is changing from query to query
    AND ROWNUM <= 3

This works well for one market/category combination, but I have to manually submit 287 queries to get all data I need. Is there any way to build a query that will do it for me with one submission?

Answer 1

Since you don't care which products are returned

SELECT product, category, market
  FROM (SELECT product, 
               category,
               market,
               row_number() over (partition by market, category
                                      order by rownum) rnk
          FROM product_table
               JOIN availability_table USING (product)
         WHERE num_available > 0)
 WHERE rnk <= 3;