[nycphp-talk] Can I do this in one query?
tom at supertom.com
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!
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?
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
Supporting Apache, MySQL and PHP
More information about the talk