query to find the top 3 in each type

Hi

I need help in finding the query which will provide the following resultset from the below table..

Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)

Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)

Result Set:
product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50

i.e The result set gives the top 3 products in each product_type based on the no_of_units.

thanksselect * from product_stocks where product_id in (select top 3 product_id from product_stocks group by product_id )order by product_type,no_of_units desc|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework|||Hi harshal,

Thanks for providing the query.
It was very helpful and met my requirement.

thanks|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework

yeah I thought it would be a RFH..:mad: .

can u please enlighten me on the flaw part please...|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!

OHH!! :confused:
I m getting lazy day by day.. need to spend more time on the forums i guess..;)

thanks for pointing out..

harshal|||Hi harshal

I tested the query , but the result is not correct .
It provides the result set for the product_id 1 , 2 , 3 in each product_type and not the
top 3 in each product_type based on no_of_units

thanks|||arjun, try this --select one.product_type
, one.product_id
, one.no_of_units
from daTable as one
inner
join daTable as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units
group
by one.product_type
, one.product_id
, one.no_of_units
having count(*) <= 3 and be sure you can explain it when your teacher asks you how you got it|||Hi

Even if order by is used in the sub query, it will give the top 3 product_id across all the product_type
But what i need is the top 3 from each of the product_type .

thanks|||Even if order by is used in the sub query, it will give the top 3 product_id across all the product_typeno, not if it's a correlated subquery

But what i need is the top 3 from each of the product_typedid you try my query?|||Hi r937 ,

I tried your query. It returns the top 3 product_id from all product_types.
I need another help.
In the result set , the order of the result set varies for each product_type.

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

How to modify this so that the no_of_units for each product_type is in the descending order.

thanks.|||I tried your query. It returns the top 3 product_id from all product_types.you could not possibly have tried it

here is what it produces:A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90this is exactly what you asked for|||Hi r937,

I am getting the same result as you have posted.

The Result set is here :

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

In this result set , the no_of_units for product_type 'A' is in descending order,
but the no_of_units for product_type 'B' and 'C' is not in descending order.

What i seek is to get the no_of_units in descending order for each of the product_types.

thanks|||look up ORDER BY in your manual

:)|||look up ORDER BY in your manual

:)
Firstly you must look up ORDER By in manual as r937 said ,thats for your knowledge..
well,this time just try this to get your results..

select one.product_type
, one.product_id
, one.no_of_units
from product_stocks as one
inner
join product_stocks as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units

group
by one.product_type
, one.product_id
, one.no_of_units

having count(*) <= 3 order by one.product_type,one.no_of_units desc

Joydeep|||This is coming pretty close to baby-sitting. arjun, you need to become familiar with books online. If you can't find your answer there, or you don't understand something, then post a question.