BuMoRi BuMoRi -4 years ago 167
MySQL Question

Increment counter in mysql and GROUP_CONCAT

Here is my mysql query:

SELECT users.id, user.name, category.id,
GROUP_CONCAT(category.name) AS catName,
@rownum := @rownum + 1 AS row_number
FROM users
JOIN usecat ON id_users=users.id
JOIN category ON id_category=category.id
CROSS JOIN (select @rownum := 0) r
GROUP BY users.id


Result: AAA-1, BBB-3, CCC-8,...

I'd like to get this result: AAA-1, BBB-2, CCC-3,...

Can you help me change this query? Thanks.

Answer Source

Try this:

SET @rownum=1;
SELECT *, @rownum := @rownum + 1 AS row_number 
FROM (SELECT users.id, user.name, category.id,
       GROUP_CONCAT(category.name) AS catName
  FROM users
  JOIN usecat ON id_users=users.id
  JOIN category ON id_category=category.id
GROUP BY users.id) t;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download