CODEWITHSUNDEEP
mysqlsql
Bloomberg
Bloomberg

Reputation: 2357

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

Upvotes: 2

Views: 80

Answers (2)

Blank
Blank

Reputation: 12378

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

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 1

Related Questions