NYCPHP Meetup

[harvey]

ahhh, yes, maybe i should read the _whole_ post before responding!
sorry...


At 09:55 AM 4/11/2005, Adam Fields wrote:

>On Mon, Apr 11, 2005 at 09:18:48AM -0400, harvey wrote:
> > I don't think that query will work in all cases. You're still not really
> > limiting the final query to 5 per category. What if there are 10 
> records in
> > a category  and they all have the same score? I think your query will
> > return all of them. Actually, that might be better than arbitrarily
> > limiting at 5 when there's a tie...
>
>Yes, that's what I meant when I said: "However, this is not guaranteed
>to give you exactly five results for each category if there are
>duplicates on points."
>
>:)
>
>
>
>
> > At 10:26 PM 4/10/2005, Adam Fields wrote:
> >
> > >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 ]
> > >
> > >_______________________________________________
> > >New York PHP Talk Mailing List
> > >AMP Technology
> > >Supporting Apache, MySQL and PHP
> > >http://lists.nyphp.org/mailman/listinfo/talk
> > >http://www.nyphp.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 ]
>
>_______________________________________________
>New York PHP Talk Mailing List
>AMP Technology
>Supporting Apache, MySQL and PHP
>http://lists.nyphp.org/mailman/listinfo/talk
>http://www.nyphp.org