On Tue, Apr 29, 2008 at 12:52 PM, whoisquilty <[EMAIL PROTECTED]> wrote: > --- In [email protected], James Keeline <[EMAIL PROTECTED]> wrote: >> >> --- whoisquilty <[EMAIL PROTECTED]> wrote: >> >> > I see the problem with my earlier post. My mistake. >> > >> > rankid (id for each row) >> > vidno (id number of the video viewed i.e. page loaded) >> > access_time (date/time it is accessed) >> > >> > So, vidno is the id number for the video. I will join another table >> > based on vidno that will show the info on the video that is viewed, >> > therefore displaying that info in the "most popular". >> > >> > Even when I group by vidno, it tells me that it can't group on >> > popular, however. >> >> SELECT vidno, COUNT(*) AS popular FROM tablename GROUP BY vidno ORDER BY >> popular DESC LIMIT 10; >> >> James >> > > Wow, that worked. Thanks, James. > > If I wanted to implement a ratings system, would I use the same > technique just with AVG()? > > Jeremy >
That would work, you'd want to AVG() the rating column rather than * though... SELECT vidno, AVG(rating) AS popular FROM tablename GROUP BY vidno ORDER BY popular DESC LIMIT 10 You might consider for either approach storing the popular as a column that you maintain when the rating is made; would improve query speed (can be indexed) for big sites. Phill
