NYCPHP Meetup

[harvey]

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...


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