[thesite] database: content table

[rudy]

> 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

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

close

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) --

select
    contentid, replies, categoryid, contentname,
        (select max(datemod) from replies
             where contentid = content.contentid) as lastcommentdate
from
    content
where
    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...)


rudy