Mysql Limit column value repetition N times

I have two tables

Customer (idCustomer, ecc.. ecc..)
Comment (idCustomer, idComment, ecc.. ecc..)

obviously the two table are joined togethe开发者_StackOverflow中文版r, for example

SELECT * FROM Comment AS co
  JOIN Customer AS cu ON cu.idCustomer = co.idCustomer

With this I select all comment from that table associated with is Customer, but now I wanna limit the number of Comment by 2 max Comment per Customer.

The first thing I see is to use GROUP BY cu.idCustomer but it limits only 1 Comment per Customer, but I wanna 2 Comment per Customer.

How can I achieve that?

One option in MySQL is server-side variables. For example:

set @num := 0, @customer := -1;

select  *
from    (
        select  idCustomer
        ,       commentText
        ,       @num := if(@customer = idCustomer, @num + 1, 1) 
                    as row_number
        ,       @customer := idCustomer
        from    Comments
        order by 
                idCustomer, PostDate desc
        ) as co
join    Customer cu
on      co.idCustomer = cu.idCustomer
where   co.row_number <= 2

This version doesn't require the SET operation:

select  *
from    (select  idCustomer
         ,       commentText
         ,       @num := if(@customer = idCustomer, @num + 1, 1) as row_number
         ,       @customer = idCustomer
         from    Comments
         JOIN(SELECT @num := 0, @customer := 1) r
         order by idCustomer, PostDate desc) as co
 join    Customer cu on co.idCustomer = cu.idCustomer
 where   co.row_number <= 2

SELECT * FROM Comments AS cm1 
         LEFT JOIN Comments AS cm2 ON cm1.idCustomer = cm2.idCustomer 
         LEFT JOIN Customer AS cu ON cm1.idCustomer = cu.idCustomer
WHERE cm1.idComment != cm2.idComment
GROUP BY cm1.idCustomer

However, if you are going to change the number of comments it's better to use Andomar's solution.

There is no need to use cursor, which is very slow. See my answer to Complicated SQL Query About Joining And Limitting. DENSE_RANK will do the trick without all cursor intricacies.

If you are using a scripting language such as PHP to process the results, you could limit the number of results shown per customer after running the query. Set up an array to hold all the results, set up another array to hold the number of results per customer and stop adding the query results to the result set after the count exceeds your limit like so:

$RESULTS = array();
$COUNTS = array();
$limit = 2;
$query = "SELECT customer_id, customer_name, customer_comment FROM customers ORDER BY RAND()";
$request = mysql_query($query); 
while ($ROW = mysql_fetch_assoc($request))
{
    $c = $ROW['customer_id'];
    $n = $COUNTS[$c];
    if ($n<$limit)
    {
         $RESULTS[] = $ROW;
         $COUNTS[$c]++;
    }
}

This guarantees only two comments per customer will be shown pulled randomly or however you want, the rest gets thrown out. Granted you are pulling ALL the results but this is (probably) faster than doing a complex join.