CODEWITHSUNDEEP
mysqlsqlruby-on-rails-3ruby-on-rails-4
Bloomberg
Bloomberg

Reputation: 2367

How to concat query result of top N records from a group in MYSQL

I have the following query which gives the count of name grouped by user id in desc order. 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 so far 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   |
+---------+----------------+

Upvotes: 1

Views: 503

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Ue group_concat():

SELECT group_id, group_concat(name order by rn) as names
FROM (SELECT t.*, 
             (@rn := IF(@grp = t.user_id, @rowno := @rowno + 1, 
                        IF(@grp := t.user_id, 1, 1)
                       )
             )  as rn
      FROM (SELECT n.user_id, t.name, n.name,  Count(t.name) ct 
            FROM notes n INNER JOIN
                 tags t 
                 ON notes.id = t.note_id 
            GROUP BY n.user_id,  t.name 
            ORDER BY n.user_id, Count(t.name) DESC
           ) t CROSS JOIN
           (SELECT @rn := 0, @grp := -1) params
     ) t
WHERE rn <= 2
GROUP BY user_id;

Note:

  • The expression for @rn and @grp is a single expression. MySQL does not guarantee the order of evaluation of expressions in a SELECT, so a single expression is the only way to safely assign the two variables.
  • The variables are initialized.
  • The WHERE clause is where the "top 2" is determined.

Upvotes: 2

Related Questions