1

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?

2
  • Do you really not care which three products you get for each market & category? Jun 12, 2012 at 23:08
  • Don't care, they are for testing purposes. I just need three that are available.
    – osjak
    Jun 12, 2012 at 23:11

1 Answer 1

2

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;
1
  • 1
    Justin, thanks for your update. I ran this query, and the job failed after runnning for 5 hours with a message that the server cannot find some object any more. I think some database component hit a timout. Maybe if the database was smaller, it would work, not sure. The lesson I have learned from this attempt is that I need to actually sit down and study SQL, instead of getting pieces of information here and there.
    – osjak
    Jun 13, 2012 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.