how to get row with the highest value in count for every user in mysql

Question

I have a table from which throuh query i have obtained this result. I am trying to get the name of every user which hast the highest count.

select  n.user_id,t.name,count(t.name) ct from
      temp_user_batches n inner join tags t on n.id=t.note_id
      where user_id IN (9122,9125,9126,9127) 
      group by n.user_id,t.name order by 1,3 desc

this query gives me this table result

   USERID   NAME                 COUNT
    9122    AWESOME               4
    9122    BritishLanguage       3
    9122    Feeling               3
    9122    fantastic             2
    9122    blessed               1
    9125    BritishLanguage       4
    9125    London                3
    9125    fantastic             3
    9125    EnglishUK             3
    9125    calmos                2
    9125    AWESOME               2
    9125    amazing               2
    9126    AWESOME               7
    9126    Feeling               3
    9126    Gary                  2
    9126    safe                  1
    9126    blessed               1
    9126    EnglishUK             1
    9127    Carl                  3
    9127    karen                 3
    9127    kelly                 2

the result i am trying to get:

   USERID   NAME                 COUNT
    9122    AWESOME               4
    9125    BritishLanguage       4
    9126    Awesome               7
    9127    Carl                  3

wouldn't 9126 have the highest count of 7 what would constitute 9122 and 9125 to be included? And what is criteria to determine including 2 of 9216's rows and not the others? — Matt, Jul 01 '16 at 21:18
hey matt thanks for pointing out it was a copy paste mistake! criteria is to get the name of every user with highest count and if there are more than one highest count for a user then i would try to pick first one. — Bloomberg, Jul 01 '16 at 21:24
See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with the max value of a column. The only difference is that you need to use your query as a subquery in place of a table name. — Barmar, Jul 01 '16 at 22:02
@Lannister can you specify primary key of tables? It may help to write simpler query. — Mostafa Vatanpour, Jul 02 '16 at 06:10
What if there's a tie? And without data sets, result sets are pretty meaningless. — Strawberry, Jul 02 '16 at 08:31

score 1 · Answer 1 · answered Jul 02 '16 at 08:55

I changed a couple of table and column identifiers - in order to aid comprehension...

SELECT x.*
  FROM 
     ( SELECT n.user_id
            , t.name
            , COUNT(t.name) ct 
         FROM notes n 
         JOIN tags t 
           ON n.note_id = t.note_id
        WHERE user_id IN (9122,9125,9126,9127)
        GROUP 
           BY n.user_id
            , t.name
     ) x
  JOIN 
     ( SELECT user_id
            , MAX(ct) ct
         FROM 
            ( SELECT n.user_id
                   , t.name
                   , COUNT(t.name) ct 
                FROM notes n 
                JOIN tags t 
                  ON n.note_id = t.note_id
               WHERE user_id IN (9122,9125,9126,9127)
               GROUP 
                  BY n.user_id
                   , t.name
            ) a
        GROUP
           BY user_id
     ) y
    ON y.user_id = x.user_id
   AND y.ct = x.ct;

Thank you for your answer. Thus I deleted my answer. – Mostafa Vatanpour Jul 02 '16 at 12:06 — Mostafa Vatanpour, Jul 02 '16 at 12:06

Blank · Accepted Answer · 2016-07-02T09:48:28.647

Try this, it will return some useless columns.

select 
    t.*,
    if(@grp = t.user_id, @rowno := @rowno + 1, @rowno := 1) as rowno,
    @grp := t.user_id
from (
    select  n.user_id,t.name,count(t.name) ct from
    temp_user_batches n inner join tags t on n.id=t.note_id
    where user_id IN (9122,9125,9126,9127) 
    group by n.user_id,t.name
    order by n.user_id, count(t.name) desc
) t
cross join (select @grp := null, @rowno := 0) v
having rowno = 1
order by 1,3 desc

how to get row with the highest value in count for every user in mysql

2 Answers2