NYCPHP Meetup

[Adam Fields]

On Mon, Apr 11, 2005 at 09:18:48AM -0400, harvey wrote:
> I don't think that query will work in all cases. You're still not really 
> limiting the final query to 5 per category. What if there are 10 records in 
> a category  and they all have the same score? I think your query will 
> return all of them. Actually, that might be better than arbitrarily 
> limiting at 5 when there's a tie...

Yes, that's what I meant when I said: "However, this is not guaranteed
to give you exactly five results for each category if there are
duplicates on points."

:)




> At 10:26 PM 4/10/2005, Adam Fields wrote:
> 
> >On Sat, Apr 09, 2005 at 12:28:39AM -0400, Tom wrote:
> >> Can I do this in one query?
> >>
> >> Suppose I have a three column table (it is really not, but I'm trying to
> >> keep it simple)
> >>
> >> table: scores
> >> name, varchar(40)
> >> category, varchar(40)
> >> points, int
> >
> >If you're on MySQL 4.1+, you ought to be able to do this with
> >something like:
> >
> >SELECT name, points, category FROM scores s
> >WHERE points IN (SELECT points FROM scores WHERE category = s.category
> >      ORDER BY points DESC LIMIT 0,5)
> >ORDER BY category, points DESC;
> >
> >(I think that's right, I haven't actually tried it.)
> >
> >However, this is not guaranteed to give you exactly five results for
> >each category if there are duplicates on points.
> >
> >> I would like to select the top 5 in each category with the most amount of
> >> points, and group them by category, to ultimately be displayed like this:
> >>
> >>
> >> CATEGORY1
> >>
> >> person1  1000
> >> person2  900
> >> person3  800
> >> person4  700
> >> person5  600
> >>
> >>
> >> CATEGORY2
> >>
> >> person1  950
> >> person2  800
> >> person3  700
> >> person4  500
> >> person5  400
> >>
> >>
> >> I see that I can do this in two queries and some PHP code, by driving a 
> >loop
> >> with the DISTINCT category, and saying something like
> >>
> >> SELECT name,points FROM scores WHERE category='$category' ORDER BY points
> >> DESC LIMIT 0,5
> >>
> >> but there must be a better way
> >>
> >>
> >> Thanks,
> >>
> >>
> >> Tom
> >> http://www.liphp.org
> >>
> >>
> >> _______________________________________________
> >> New York PHP Talk Mailing List
> >> AMP Technology
> >> Supporting Apache, MySQL and PHP
> >> http://lists.nyphp.org/mailman/listinfo/talk
> >> http://www.nyphp.org
> >
> >--
> >                                - Adam
> >
> >** I can fix your database problems: 
> >http://www.everylastounce.com/mysql.html **
> >
> >Blog............... [ http://www.aquick.org/blog ]
> >Links.............. [ http://del.icio.us/fields ]
> >Photos............. [ http://www.aquick.org/photoblog ]
> >Experience......... [ http://www.adamfields.com/resume.html ]
> >Product Reviews: .. [ http://www.buyadam.com/blog ]
> >
> >_______________________________________________
> >New York PHP Talk Mailing List
> >AMP Technology
> >Supporting Apache, MySQL and PHP
> >http://lists.nyphp.org/mailman/listinfo/talk
> >http://www.nyphp.org
> 
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org

-- 
				- Adam

** I can fix your database problems: http://www.everylastounce.com/mysql.html **

Blog............... [ http://www.aquick.org/blog ]
Links.............. [ http://del.icio.us/fields ]
Photos............. [ http://www.aquick.org/photoblog ]
Experience......... [ http://www.adamfields.com/resume.html ]
Product Reviews: .. [ http://www.buyadam.com/blog ]