Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    55

    Unanswered: top 3 of all top products (was "need help with a query")

    Consider the below tables

    Table: Products
    ----------------------------------------------------
    | pid | pname | ranking | cat1 | cat2 | cat3 | mid |
    ----------------------------------------------------
    | 1 | x1 | 20 | 2 | 3 | -1 | 1 |
    | 2 | x2 | 40 | 3 | 2 | -1 | 1 |
    | 3 | x3 | 80 | 2 | 4 | -1 | 1 |
    | 4 | y1 | 40 | 2 | -1 | -1 | 2 |
    | 5 | y2 | 60 | 1 | 3 | -1 | 2 |
    | 6 | z1 | 50 | 3 | -1 | -1 | 3 |
    | 7 | z2 | 70 | 1 | 3 | -1 | 4 |
    ----------------------------------------------------

    Table: Manufacturers
    ---------------
    | mid | mname |
    ---------------
    | 1 | A1 |
    | 2 | B2 |
    | 3 | C3 |
    ---------------

    Table: Categories
    ---------------
    | cid | cname |
    ---------------
    | 1 | I1 |
    | 2 | J2 |
    | 3 | K3 |
    ---------------

    Say if I want to get the top 3 products that fall under category "J2" and have them ordered by ranking then I would use the

    below query

    ELECT DISTINCT TOP 3 mid, pid, pname, prank, mname
    FROM Products, Manufacturers, Categories
    WHERE Products.mid = Manufacturers.mid AND (cat1=2 OR cat2=2 OR cat3=2)
    ORDER BY ranking


    Now what if I need the top 3 ranked products that fall under category "J2" and have them ordered by ranking selecting only

    one product from each manufacturer. So in order words I want the top ranked product from each manufacturer.

    What would that query be?

    I am having a really tough time and have already spent quite sometime on it unsuccessfully. I would appreciate any help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    top 3 of all single top-ranked products by manufacturer --
    Code:
    select top 3
           pid
         , pname
         , ranking
         , cat1
         , cat2
         , cat3
         , mid
      from Products as P
     where ranking
         = ( select max(ranking)
               from Products
              where mid = P.mid ) 
    order by ranking desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    55
    Thanks for your reply.

    What if I also wanted to get the mname and cname? How can I modify the current query to get them?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you would do a simple join to get them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2004
    Posts
    55
    So the query would be
    select top 3 pid, pname, ranking, cat1, cat2, cat3, mid, mname, cname
    from Products as P, Manufacturers as M , Categories as C
    where ranking
    = ( select max(ranking)
    from Products
    where mid = P.mid ) AND P.mid=M.mid AND C.cid=2 AND (cat1=2 OR cat2=2 OR cat3=2)
    order by ranking desc

    In both the above queries if 2 products under a manufacturer is ranked the same, then both of them will appear. So I have included a new field called "Last Updated Date". So if the product is updated, the date is updated.

    Is there any way to avoid the duplicates using this new field?

    My SQL skills is not very strong.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ties in ranking are a non-trivial problem

    best would be if you could go through the data and ensure that rankings are unique

    otherwise, the sql will get very complicated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2004
    Posts
    55
    The problem is that I have a table with over 4000 records. So its going through them will be really time consuming to go through the records.

    I just spent the last hour working on the query and was unsuccessful. I would appreciate any help.

  8. #8
    Join Date
    Dec 2004
    Posts
    55
    any help with the query?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is mildly drug-induced, but I think it will produce something close to what you want. The problem is (as Rudy pointed out), it doesn't get finicky about ties, it returns all of the top ranked products for a given manufacturer in a given category for your computing pleasure. The code goes something like:
    Code:
    SELECT a.pid
       FROM products AS a
       JOIN categories AS c1
          ON ('J2' = c1.name
          AND c1.cid IN (a.cat1, a.cat2, a.cat3))
       WHERE  a.ranking = (SELECT Max(p.ranking)
             FROM products AS p
             WHERE  p.mid = a.mid
                AND c1.cid IN (p.cat1, p.cat2, p.cat3))
       ORDER BY a.ranking DESC
    -PatP

Posting Permissions

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