NYCPHP Meetup

[Daniel Convissor]

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

-- 
 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
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409