Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Talking Unanswered: Top Five Of Each Group

    I want to figure how I can get the top 3 products sold for each product class. I have table as the following. (This an example, I have alot more products and a lot product classes)

    Product Class Product Sold
    Glass A 13
    Glass B 10
    Glass C 15
    Glass D 7
    Glass E 12
    Wood F 15
    Wood G 17
    Wood H 5
    Wood I 0
    Wood J 10

    I want the result to come out like this..


    Product Class Product Sold
    Glass C 15
    Glass A 13
    Glass E 12
    Wood G 17
    Wood F 15
    Wood J 10

    Can anyone please help me?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How do you want to deal with "ties", for example:
    Code:
    Product Sold	Product Class
    12		Thingie
    15		Thingie
    7		Thingie
    12		Thingie
    12		Thingie
    12		Thingie
    7		Thingie
    15		Thingie
    7		Thingie
    Would it produce:
    Code:
    Product Sold	Product Class
    15		Thingie
    12		Thingie
    7		Thingie
    or maybe
    Code:
    Product Sold	Product Class
    15		Thingie
    15		Thingie
    12		Thingie
    12		Thingie
    12		Thingie
    12		Thingie
    7		Thingie
    7		Thingie
    7		Thingie
    Each of these are valid answers, using different rules for how to count the "three" part!

    -PatP

  3. #3
    Join Date
    Sep 2004
    Posts
    3
    I would like it to show

    Product Sold Product Class
    15 Thingie
    15 Thingie
    12 Thingie
    12 Thingie
    12 Thingie
    12 Thingie
    7 Thingie
    7 Thingie
    7 Thingie

    of course it being the top 3, it would look like this:

    Product Sold Product Class
    15 Thingie
    15 Thingie
    12 Thingie

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the data that you've presented, I don't think there is a way to do that without using iteration of some kind. If you could add a unique column to the table, then it would be possible.

    -PatP

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    SELECT Product_Class, Product_Sold
    FROM table t1
    WHERE 3 > (SELECT count(*) FROM table t2 WHERE t1.Product_Sold < t2.Product_Sold and t2.Product_Class = t1.Product_Class)

  6. #6
    Join Date
    Sep 2004
    Posts
    3
    urquel,
    That work beautifuly. The part that is pissing me off is I am trying to figure out the logic behind it. I think I am getting a brain cramp. Well, it doesn't matter, as long as it worked. Thanks!

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    Just think of it like this. For each row in the query (t1), are there fewer than 3 rows in the correlated subquery (t2) that have a greater value. If there are, you qualify for the top 3 list.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •