SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    Query throwing me for a loop...

    Alright, I have a table that looks like:

    Code:
    order_id | prod_id | prod_ppu | quantity
    It's an associative entity. What I need to select is the top 6 sellers (so the products with the highest quantity bought) and display them. The problem I am running in to is that I cannot select MAX() as that will return the highest product bought on that order.

    I am just at a loss here. I hope my explanation is clear.

    Any ideas?

    P.S. - I can't use subqueries...

  2. #2
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it not as simple as...
    Code:
    SELECT
    order_id, prod_id, prod_ppu, quantity
    FROM
    yourtable
    ORDER BY
    quantity
    DESC LIMIT 6
    Lats...

  3. #3
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    No, because there will be multiple records in the table with the same order_id (just a different prod_id).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select prod_id
         , sum(quantity) as totalqty
      from yourtable
    group
        by prod_id
    order
        by totalqty desc
    limit 6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent! I was wrestling with a similar issue.
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."



Bookmarks

Posting Permissions

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