Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2006
    Posts
    9

    Unanswered: 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.

    thanks

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    select * 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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2006
    Posts
    9
    Hi harshal,

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

    thanks

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by r937
    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.. .

    can u please enlighten me on the flaw part please...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by r937
    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!!
    I m getting lazy day by day.. need to spend more time on the forums i guess..

    thanks for pointing out..



    harshal

  8. #8
    Join Date
    Jan 2006
    Posts
    9
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arjun, try this --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2006
    Posts
    9
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arjun_me
    Even if order by is used in the sub query, it will give the top 3 product_id across all the product_type
    no, not if it's a correlated subquery


    Quote Originally Posted by arjun_me
    But what i need is the top 3 from each of the product_type
    did you try my query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2006
    Posts
    9
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arjun_me
    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:
    Code:
    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
    this is exactly what you asked for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2006
    Posts
    9
    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

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look up ORDER BY in your manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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