A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearHow do I find the most common result in a column in my MySQL table - page 1
User InfoPosts
How do I find the most common result in a column in my MySQL table#1
Using PHP and MySQL, I want to query a table of postings my users have made to find the person who has posted the most entries.

What would be the correct query for this?

Sample table structure:


[id] [UserID]
1 johnnietheblack
2 johnnietheblack
3 dannyrottenegg
4 marywhite
5 marywhite
6 johnnietheblack


I would like to see that "johnnietheblack" is the top poster, "marywhite" is second to best, and "dannyrottenegg" has the least

posted date: 2009-04-10 15:21:00


Re: How do I find the most common result in a column in my MySQL table#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-10 15:21:01


Re: How do I find the most common result in a column in my MySQL table#3
Can you provide the table structure for the postings table?

posted date: 2009-04-10 15:22:00


Re: How do I find the most common result in a column in my MySQL table#4
Yup, that would help ;-)

posted date: 2009-04-10 15:26:00


Re: How do I find the most common result in a column in my MySQL table#5
I believe this should work...SELECT user_id, COUNT(*) FROM postings ORDER BY COUNT(*) GROUP BY user_id LIMIT 1

posted date: 2009-04-10 15:27:00


Re: How do I find the most common result in a column in my MySQL table#6
Something like:SELECT COUNT(*) AS `Rows`, UserIDFROM `postings`GROUP BY UserIDORDER BY `Rows` DESCLIMIT 1This gets the number of rows posted by a particular ID, then sorts though the count to find the highest value, outputting it, and the ID of the person. You(ll need to replace the (UserID( and (postings( with the appropriate column and field though.

posted date: 2009-04-10 15:28:00


Re: How do I find the most common result in a column in my MySQL table#7
Assuming posting is a tuple (user_id, recipient_user_id), where each row represents one posting, from user_id to recipient_user_id:select user_id, count(*) as posts from postingsgroup by user_idhaving count(*) = max(count(*)) ;

posted date: 2009-04-10 15:30:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.