I have the following query which gives the count of name grouped by user id. I have reached so far but cant go beyond that. I would like to concat the name column of top 2 records per user.
The Query is:
SELECT t.*,
IF(@grp = t.user_id, @rowno := @rowno + 1, @rowno := 1) AS rowno,
@grp := t.user_id AS u_id
FROM (SELECT notes.user_id,
t.name name,
Count(t.name) ct
FROM notes
INNER JOIN tags t
ON notes.id = t.note_id
GROUP BY notes.user_id,
t.name
ORDER BY notes.user_id,
Count(t.name) DESC) t;
It gives the following result:
+---------+------------+----+-------+-----+
| user_id | name | ct | rowno | uid |
+---------+------------+----+-------+-----+
| 282 | realifex | 1 | 1 | 282 |
+---------+------------+----+-------+-----+
| 282 | clear | 1 | 2 | 282 |
+---------+------------+----+-------+-----+
| 282 | thinking | 1 | 3 | 282 |
+---------+------------+----+-------+-----+
| 282 | refreshing | 1 | 4 | 282 |
+---------+------------+----+-------+-----+
| 285 | solid | 2 | 1 | 285 |
+---------+------------+----+-------+-----+
| 285 | clear | 1 | 2 | 285 |
+---------+------------+----+-------+-----+
| 285 | thinking | 1 | 3 | 285 |
+---------+------------+----+-------+-----+
| 287 | holidays | 3 | 1 | 287 |
+---------+------------+----+-------+-----+
| 287 | Larry | 3 | 2 | 287 |
+---------+------------+----+-------+-----+
| 287 | travel | 2 | 3 | 287 |
+---------+------------+----+-------+-----+
| 287 | thinking | 1 | 4 | 287 |
+---------+------------+----+-------+-----+
I am trying to concat the top 2 results into one column from every user group like this:
+---------+----------------+
| user_id | name |
+---------+----------------+
| 282 | realifex,clear |
+---------+----------------+
| 285 | solid, clear |
+---------+----------------+
| 287 | Larry,travel |
+---------+----------------+
Aucun commentaire:
Enregistrer un commentaire