[!--quoteo(post=336617:date=Jan 14 2006, 09:44 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 14 2006, 09:44 PM) 336617[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I don't follow at all... you refer to members and pictures, and I don't see either in your query. But if you want to get the group-wise maximum (e.g. of a COUNT), then you're going to need a subselect or a temporary table -- you can't do this with JOINs.
Hi Fenway,
What i have is a table named "pictures" with colums "id","submitter"(as a number)
another table called "members" with colums "id" (which relates exactly to the submitter colum above),
"nickname"
and another called "challenges" with colums "id", "champion_id","champion_votes","challenger_id","challenger_votes","winner"(holds the id number of the picture that one)
what i`m trying to do is display the top ten members nicknames that have one the most battles.
this select:
SELECT winner, COUNT(*) as ccount FROM challenges WHERE winner IS NOT NULL group by winner order by ccount desc limit 10
selects the top ten pictures that have one the most battles but i now need to find out who submitted them pictures and display there nicknames in order.
Now the "id" in the pictures table holds the related value to the result the select above produces and the "submitter" value in the pictures table then tells you who submitted that picture then its just a case of
incorporating the original line you helped me with to convert the "submitter" value to his nickname.
Is that at all possible with one select or do i have to do it some other way.
Excuse my ignorance i`m searching and reading books like crazy but getting no where.
All the best
munster