NYCPHP Meetup

[Tom Melendez]

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

-- 
 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
_______________________________________________
New York PHP Talk Mailing List
AMP Technology
Supporting Apache, MySQL and PHP
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org