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?

How do you want to deal with "ties", for example:Would it produce:or maybeEach of these are valid answers, using different rules for how to count the "three" part!

-PatP

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

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

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)

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!

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.