[nycphp-talk] Can I do this in one query?

Tom Melendez tom at
Sun Apr 10 12:05:15 EDT 2005

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!


-----Original Message-----
From: talk-bounces at [mailto:talk-bounces at] 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.


 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409
New York PHP Talk Mailing List
AMP Technology
Supporting Apache, MySQL and PHP

More information about the talk mailing list