Q:

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

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

A:

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

