<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi all. I need to create a block (with views module) that contain last
nodes that have new comments.<br>
<br>
The view that I have created return me this SQL:<br>
<br>
SELECT DISTINCT(node.nid) AS nid, term_data.name AS term_data_name,
term_data.vid AS term_data_vid, term_data.tid AS term_data_tid,
node.title AS node_title, node.created AS node_created,
node_comment_statistics.comment_count AS
node_comment_statistics_comment_count, comments.timestamp AS
comments_timestamp FROM node node LEFT JOIN term_node term_node ON
node.vid = term_node.vid LEFT JOIN term_data term_data ON term_node.tid
= term_data.tid INNER JOIN node_comment_statistics
node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT
JOIN comments comments ON node.nid = comments.nid WHERE (node.type in
('articolo_corto', 'articolo_lungo', 'articolo_medio', 'dossier',
'story')) AND (node.status <> 0) AND (term_data.vid in ('1'))
ORDER BY comments_timestamp DESC<br>
<br>
<div>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<span class="Apple-style-span"
style="border-collapse: separate; color: rgb(0, 0, 0); font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; font-size: medium;"><span
class="Apple-style-span"
style="border-collapse: collapse; color: rgb(73, 73, 73); font-family: Verdana,sans-serif; font-size: 12px; line-height: 20px;"></span></span><span
class="syntax"><span class="syntax_digit syntax_digit_integer"><br>
But there is a problem: results are not unique. <br>
<br>
Which is the correct SQL? "Group by nid" and "group by node_title"
return me a non correct ordered list. <br>
<br>
Tnx.<br>
<br>
M.<br>
</span></span></div>
<pre class="moz-signature" cols="72">--
Michel 'ZioBudda' Morelli <a class="moz-txt-link-abbreviated" href="mailto:michel@ziobuddalabs.net">michel@ziobuddalabs.net</a>
Sviluppo applicazioni CMS DRUPAL e web dinamiche (LAMP+Ajax)
Telefono: 0200619074
Telefono Cell: +39-3939890025 -- Fax: +39-0291390660
<a class="moz-txt-link-freetext" href="http://www.ziobudda.net">http://www.ziobudda.net</a> ICQ: 58351764
<a class="moz-txt-link-freetext" href="http://www.ziobuddalabs.it">http://www.ziobuddalabs.it</a> Skype: zio_budda
<a class="moz-txt-link-freetext" href="http://www.ziodrupal.net">http://www.ziodrupal.net</a> MSN: <a class="moz-txt-link-abbreviated" href="mailto:michel@ziobuddalabs.it">michel@ziobuddalabs.it</a>
JABBER: <a class="moz-txt-link-abbreviated" href="mailto:michel@ziobuddalabs.it">michel@ziobuddalabs.it</a>
</pre>
</body>
</html>