SELECT DISTINCT and ORDER BY date

How do I display renewed items?

[Kuba]

I have a news site that allows users to add comments to articles, so I would like to show 10 recently commented stories in a side box. I am trying to use the following code ('sid' is article's id, 'date' is comment's date):

$query="SELECT DISTINCT c.sid FROM comments c, articles a WHERE c.sid = a.sid ORDER BY c.date DESC LIMIT 10";
$result=mysql_query($query);
while (list($sid) = mysql_fetch_row($result)) {
$query2="SELECT date, title FROM comments WHERE sid=$sid ORDER BY date DESC LIMIT 1";
$result2=mysql_query($query2);
list($date, $title) = mysql_fetch_row($result2);
echo "$title - $date<br>";
}

If there is one comment to each article, the box shows fine:

Article A - 08:35
Article B - 07:27
Article C - 05:05
etc...

However, when there are multiple comments, it may show like this:

Article A - 08:35
Article C - 05:05
Article B - 07:27
etc...

The time of the latest comment to each article shows correctly, but the articles are not ordered by the latest comment, but by the earliest comment. In other words, Article B goes after Article C because its first comment is dated earlier than the first comment to Article B.

I understand that SELECT DISTINCT in my query finds the first instance of a comment to any given article and stops there, going further to the next article, etc.

How should I change the query to display articles in the desired order?

[dmmh]

$query="SELECT * FROM articles a ORDER BY a.sid DESC LIMIT 10";
$result=mysql_query($query);

while (list($sid) = mysql_fetch_row($result)) {

$query2="SELECT date, title FROM comments WHERE sid=$sid ORDER BY date DESC";
$result2=mysql_query($query2);
list($date, $title) = mysql_fetch_row($result2);

echo "$title - $date<br>";
}

I think this would do it :)

[Kuba]

Thank you for your input but this will retrieve 10 latest articles in the order of their creation, not in the order of their renewal, the renewal date being the date of last posted comment in the comments table. Is this task possible, or should I add a new column in the articles table for the renewal date? What do you think?

[hakre]

hi kuba,

the other suggestion means 10 extra queries for nothing. whatever, i thought about grouping and using the MAX function thereby. i have no clue on the way you join the tables (cross way for me), but maybe this quick shoot is helpfull:

SELECT a.*, MAX(c.date) AS dtmax FROM articles AS a, comments AS c WHERE c.sid = a.sid GROUP BY a.sid ORDER BY dtmax DESC LIMIT 10;

this should only pick up the one comment's date that has the highest value per a.sid (and that's then c.sid) because of the MAX() function.

-hakre

[Kuba]

Grouping and MAX did the trick. Thank you, hakre!

[dmmh]

omg, my bad lol, I wrote that query in the morning, guess I shouldnt have :D