NYCPHP Meetup

[Adam Fields]

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 ]