NYCPHP Meetup

[harvey]

this will get you all your results in one query
but unfortunately doesn't limit to 5 per category
you can do that when you're printing the results
(or of course maybe there's some fancier query that can do it for you)
select name, points from scores group by category asc order by points desc


At 12:28 AM 4/9/2005, 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
>
>
>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