Page 1 of 1

Simple, Most Topic

Posted: Fri Mar 06, 2009 9:36 am
by SheDesigns
I'm having a brain fart! :x

I need to find what topics the user has the most entries in.

select msg_author, msg_topic from messages where msg_author = 'mary';

msg_author msg_topic
mary diet
mary diet
mary exercise
mary recipes
mary recipes
mary recipes
mary recipes
mary yoga
mary yoga

So obviously, Mary's most popular topic is "recipes".

1) How could I write a query that would pull her most used msg_topic?
2) If 2, or 3 msg_topics are equal, I would like all of the topics to display.

Re: Simple, Most Topic

Posted: Fri Mar 06, 2009 10:12 am
by pickle
A query with COUNT() and GROUP BY() should do it.

Re: Simple, Most Topic

Posted: Fri Mar 06, 2009 10:20 pm
by susrisha
havent checked much or did any research but try this

Code: Select all

 
select topic, max(topic_count) from (Select count(msg_topic ) as topic_count,msg_topic as topic from messages where msg_author='mary');