NYCPHP Meetup

[Aaron Deutsch]

Can't you also use the LIMIT keyword to just grab 5?


/I'm a php-mysql newbie


Tom Melendez wrote:
> Thanks Dan and Harvey,
> 
> It is actually normalized, with category in its own table.  I was just
> trying to keep it simple.
> 
> I came to the same conclusion that you both did, so that's what I went with.
> 
> Thanks for the help!
> 
> Tom
> 
> http://www.liphp.org
>  
> 
> -----Original Message-----
> From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
> Behalf Of Daniel Convissor
> Sent: Saturday, April 09, 2005 4:07 PM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] Can I do this in one query?
> 
> Hi Tom:
> 
> 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
> 
> 
> Your question aside, you've got to normalize.  Category, and possibly name,
> should be integers with the full text in another table.
> 
> 
> 
>>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:
> 
> 
> I can't quickly think of a way to get the first five in a query.  But, 
> what I'd do is run one query:
> 
>    select * from scores
>    group by category, person
>    order by category asc, points desc
> 
> Then loop through the result set.  After the first five, stop displaying
> but continue looping until the category changes.  Then print those 5, 
> etc...  Make sure to put in some logic to deal with tie scores.
> 
> --Dan
>