Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do you really not care which three products you get for each market & category? –  Justin Cave Jun 12 '12 at 23:08
    
Don't care, they are for testing purposes. I just need three that are available. –  osjak Jun 12 '12 at 23:11
add comment

1 Answer

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;
share|improve this answer
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 '12 at 23:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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