Query throwing me for a loop...

[Hartmann]

Alright, I have a table that looks like:

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

[Lats]

Is it not as simple as...

SELECT
order_id, prod_id, prod_ppu, quantity
FROM
yourtable
ORDER BY
quantity
DESC LIMIT 6

[Hartmann]

No, because there will be multiple records in the table with the same order_id (just a different prod_id).

[r937]

select prod_id
     , sum(quantity) as totalqty
  from yourtable
group
    by prod_id
order
    by totalqty desc
limit 6

[garlinto]

Excellent! I was wrestling with a similar issue.