Mysql Limit column value repetition N times

Question

Hi at all, is my first question here, so be patient ^^

I'll go directly to problem, I have two table Customer (idCustomer, ecc.. ecc..) Comment (idCustomer, idComment, ecc.. ecc..)

obviosly the two table are joined togheter, 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 limit only 1 Comment per Customer, but I wanna 2 Comment per Customer..

how now to proceed?

Answer 1

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

Answer 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

Answer 3

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.