numbering rows by mysql

[Lwis]

Situation:

I have this table in my mysql database:

table name - cart_product

id cart_id product_id quantity
1 2 15 2
2 2 24 1
3 2 65 5
4 3 24 1

Explain table:

As you can see there are two carts or cart_ids now: 2 and 3.
In cart 2 someone has 3 products with ids (15, 24 and 65) the last cart has only one product - id 24. Also quantity shows how many of each products someone wants to have.

What I already did:

I have already written a query which selects most popular products and highest quantities. It looks like this:

SELECT product_id, COUNT(*) as C, SUM(quantity) FROM cart_product GROUP BY product_id ORDER BY C, quantity DESC;

So I get that product id 24 has two orders and 2 of quantity, then the rest of the products.

But what is the problem?

So we know that product id 24 is leading. It has 2 orders and it is the highest result. Thats great, but if I sort this table by something else (i.e. product_id) I want to have a cell in my HTML table where it would be written that this product is in the first place even though according to sort command it went to the bottom of the HTML page.

Question:

Is it possible to achieve this by adding something to mysql query or else I'll have to play some tricks with PHP. So if someone knows any mysql trick, tell me.

Thanks for any help.

[Kudose]

this product is in the first place even though according to sort command it went to the bottom

Try reversing your sort command.

i.e.

From sort() to rsort()

-or-

From DESC to ASC

[leatherback]

Hi,

Not sure, but I think there must be a more efficient way. If not.. I would use a second query.

You get your records. All fine. Then you do the order-by-count query, and retrieve all records.

Retrieve each record in the following while loop:

$1=0;
$position = array();
while($row = mysql_fetch_array($query))
  {
   $position[$row[IDCOLUM]] = $i;
   $i++;
   }

// now use it:

echo "This product is number $position[$id] in our best sellers list";