[thesite] database: content table

rudy r937 at interlog.com
Fri May 25 22:05:55 CDT 2001

> Now, I'm assuming [the replies column in the content record] was
> added to avoid a count(replies.commentid) (and joining the
> replies table at all) in the "Most Commented On" box.

hi matt

i think it was originally conceived just to show the count on the synopsis
on the home or category or search result page...  but you're right, the
purpose is to avoid the join

the column is updated every time a comment is added to an article

>    content.contentid, replies, categoryid, contentname,
>    content,
>    replies
>    replies > 13
>    and contentid <> 3509
>    and replies.contentid=content.contentid
>order by replies.datemod, replies desc


any time you have an aggregate function in the select list, along with one
or more other columns, then you must have a group by clause which contains
those other columns

the only time you don't need a group by is if the select list contains only
aggregate functions, then of course you get one row for the result set

please ask for examples if you want them

as to your query, matt, what exactly is it you're looking for?  if all you
want is the highest date, at the same time as retrieving the other stuff
about an article, then you probably want to use something like this
(caution: not tested) --

    contentid, replies, categoryid, contentname,
        (select max(datemod) from replies
             where contentid = content.contentid) as lastcommentdate
    replies > 13
    and contentid <> 3509

where did you get the (arbitrary?) number 13 from?  i know you are
selecting articles with more than 13 comments, but why 13

and what's contentid <> 3509?  (sorry, i'm too lazy to go into the database
to look...)


More information about the thesite mailing list