NYCPHP Meetup

[Ophir Prusak]

You didn't mention what database and version you're using.
I'm pretty sure this is possible with oracle or MS SQL (and probably
postgresql).
I think it might be possible with mysql 4.1 (that supports sub queries I think).

ophir

On Apr 9, 2005 12:28 AM, Tom <tom at supertom.com> 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
>