Post reply

Stored procedure to get "top 10 per category"

  • April 28, 2007 at 5:10 am

    #103352

    Hi all,

    I'm new to the board so not sure if this is the right place to post this - I'm just trying to work out, is it possible or sensible to use a stored procedure to generate the top 10 items from a set of categories?

    Basically what I have is a "Brands" table, and a "Products" table. The products table could contain 1,000s of products for each brand, so I only want to show the user the first 10 products per brand, then they can click a link to view all products. The tables are as follows:

    Brands

    BrandID (Key)

    BrandName

    Products

    ProductID (Key)

    ProductName

    BrandID (Foreign Key)

    Many thanks in advance,

    Russ

  • April 29, 2007 at 7:13 am

    #703155

    First (or top) 10 records according to what?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • April 29, 2007 at 9:19 am

    #703160

    In SQL, there is no ordering to records, so in order to get the top 10, you need to specify some order, as Peter mentioned.

    Is it top 10 by name, by sales, by something else?

  • April 29, 2007 at 12:26 pm

    #703171

    SELECT p1.BrandID,

           p1.ProjectID

    FROM   Products AS p1

    WHERE  p1.ProjectID IN (SELECT TOP 10 p2.ProjectID FROM Products AS p2 WHERE p2.BrandID = p1.BrandID ORDER BY p2.ProjectID DESC)

     

    N 56°04'39.16"
    E 12°55'05.25"

  • April 29, 2007 at 11:33 pm

    #703202

    according to probability of the customer to purchase that product, ideally.

    ---------------------------------------
    elsasoft.org

  • April 30, 2007 at 3:53 am

    #703234

    Sorry - should've mentioned that, I'm simply after the products sorted alphabetically for the time being to keep things simple.  However, I think the answer has already been given by Peter there.  Many thanks,

    Russ

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply