Mysql Limit column value repetition N times

Question

I have two tables

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

obviously the two table are joined together, 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?

Andomar · Accepted Answer · 2010-06-14T17:10:59.037

4

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

edited Jun 14 '10 at 17:10

answered Jun 14 '10 at 14:28

Andomar

232,371
49
380
404

seems that's no other different way.. I'm a little sad on this.. I see a lot of example but seems that this is the best solution. I accept it, with a little of sadness, I preferred a more elegant solution :( extracting data in this mode, you suggest a different or better db layout? – Massimo Jun 14 '10 at 14:40
1

@Paper-bat: Other databases support more elegant solutions. But MySQL has issues with `limit` in subqueries, and does not support `row_number()`. Your DB layout is fine I think – Andomar Jun 14 '10 at 14:52
1

this line @customer = idCustomer should not be @customer := idCustomer ?? – Massimo Jun 14 '10 at 16:21

score 2 · Answer 2 · answered Jun 14 '10 at 14:47

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

score 2 · Answer 3 · edited May 23 '17 at 12:18

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.

edited May 23 '17 at 12:18

Community

1
1

answered Jun 14 '10 at 14:58

Vitalii Fedorenko

110,878
29
149
111

mh.. not bad.. :) in my country (italy) is used to name it, as 'barbatrucco' :P translated it as a smart trick ^^ – Massimo Jun 14 '10 at 16:28

score 0 · Answer 4 · edited May 23 '17 at 11:54

0

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.

edited May 23 '17 at 11:54

Community

1
1

answered Jan 11 '11 at 02:57

Schultz9999

8,717
8
48
87

score 0 · Answer 5 · answered Feb 01 '12 at 16:21

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.

I would like to use only a sql query because is faster and clear when reading code, and when you need to update your code in the future you can simply modify your sql, with minor changes on scripting code. That's why I spend a lot of time when I create a sql query, more specific query, less code and in the end you can learn something new Thanks anyway for your contribute — Massimo, Apr 03 '12 at 14:29

Mysql Limit column value repetition N times

5 Answers5

Linked