[nycphp-talk] Can I do this in one query?
Adam Fields
fields at hedge.net
Sun Apr 10 22:26:07 EDT 2005
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
If you're on MySQL 4.1+, you ought to be able to do this with
something like:
SELECT name, points, category FROM scores s
WHERE points IN (SELECT points FROM scores WHERE category = s.category
ORDER BY points DESC LIMIT 0,5)
ORDER BY category, points DESC;
(I think that's right, I haven't actually tried it.)
However, this is not guaranteed to give you exactly five results for
each category if there are duplicates on points.
> 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:
>
>
> CATEGORY1
>
> person1 1000
> person2 900
> person3 800
> person4 700
> person5 600
>
>
> CATEGORY2
>
> person1 950
> person2 800
> person3 700
> person4 500
> person5 400
>
>
> I see that I can do this in two queries and some PHP code, by driving a loop
> with the DISTINCT category, and saying something like
>
> SELECT name,points FROM scores WHERE category='$category' ORDER BY points
> DESC LIMIT 0,5
>
> but there must be a better way
>
>
> Thanks,
>
>
> Tom
> http://www.liphp.org
>
>
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
--
- Adam
** I can fix your database problems: http://www.everylastounce.com/mysql.html **
Blog............... [ http://www.aquick.org/blog ]
Links.............. [ http://del.icio.us/fields ]
Photos............. [ http://www.aquick.org/photoblog ]
Experience......... [ http://www.adamfields.com/resume.html ]
Product Reviews: .. [ http://www.buyadam.com/blog ]
More information about the talk
mailing list