php mysql 2选择1选择

Question

id | cid | name 
 1 | 1   | product 1 
 2 | 1   | product 2 
 3 | 1   | product 3 
 4 | 1   | product 4 
 5 | 1   | product 5 
 6 | 1   | product 6 
 7 | 2   | product 7 
 8 | 2   | product 8
 9 | 2   | product 9 
10 | 2   | product 10 
11 | 2   | product 11 
12 | 2   | product 12 
13 | 3   | product 13 
14 | 3   | product 14
15 | 3   | product 15
16 | 3   | product 16 

PHP:

$query  = "SELECT cid FROM product GROUP BY cid  ORDER by cid DESC";
$result = mysql_query($query) or die('Error : ' . mysql_error()); 

echo 'id |cid | name  <br>';

while($row = mysql_fetch_array($result, MYSQL_NUM))
{
    list($cid) = $row;

    $query2  = "SELECT id, cid, name FROM product WHERE cid  = $cid ORDER by id DESC LIMIT 3";
    $result2 = mysql_query($query2) or die('Error : ' . mysql_error());

    while($row2 = mysql_fetch_array($result2, MYSQL_NUM)){
        list($id2, $cid2, $name2) = $row2;
        echo ''.$id2.' | '.$cid2.' | '.$name2.'  <br>';
    }   

}

return ==>>

id | cid | name 
16 | 3   | product 16 
15 | 3   | product 15 
14 | 3   | product 14 
12 | 2   | product 12 
11 | 2   | product 11 
10 | 2   | product 10 
 6 | 1   | product 6 
 5 | 1   | product 5 
 4 | 1   | product 4 

Now, i wan't 2 select to 1 select.

example:

    $query  = "SELECT cid FROM product WHERE (SELECT id, cid, name FROM product WHERE cid  = $cid ORDER by id DESC LIMIT 3) GROUP BY cid  ORDER by cid DESC";
    $result = mysql_query($query) or die('Error : ' . mysql_error());

    while($row = mysql_fetch_array($result, MYSQL_NUM)){
        list($id, $cid, $name) = $row;
        echo ''.$id.' | '.$cid.' | '.$name.'  <br>';
    }

return ==>>

id | cid | name 

16 | 3   | product 16 
15 | 3   | product 15 
14 | 3   | product 14
12 | 2   | product 12 
11 | 2   | product 11 
10 | 2   | product 10 
 6 | 1   | product 6 
 5 | 1   | product 5 
 4 | 1   | product 4 

help me! thanks for support

Answer 1

Easiest way to achieve it would be as

SELECT *
FROM   product p
WHERE (
  SELECT  COUNT(*) 
  FROM    product  p1
  WHERE p1.cid = p.cid AND 
  p.id <= p1.id
) <= 3
order by p.id desc

DEMO