Lannister Lannister - 5 months ago 7
MySQL Question

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

Answer

This answer is not complete. I will fix it ASAP...

Try this :

select distinct x.* from temp_user_batches x join
(select user_id,max(count) maxi from temp_user_batches group by user_id) y
on(x.user_id=y.user_id and x.count=y.maxi);