NYCPHP Meetup

[Tom]

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