Select statement

[tomato]

I have a table that is part of a "post a comment system" with the following fields.

commentid, pluginid, objectid, date, username, comment

The code below will return multiple entries with the same same objectid. Leaving my "latest comments" section with several entries for the same object (a forum post). This screen shot below should clarify my issue. I only need to return the last comment from the last few entries, not 3 entries from the same object if they where posted one after another. I hope I was clear explaining this.


[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ORDER BY DATE DESC LIMIT 0, $limit");

  // display comments

  while($comment = $DB->fetch_array($getcomments))
  {

      $blog = $DB->query_first("SELECT * FROM " . TABLE_PREFIX . "p819_personal_blog WHERE blog_id = '".$comment['objectid']."'"); [/code]


[img src=\"http://www.jaspersrealm.com/tmp/sql_issue.png\" border=\"0\" alt=\"IPB Image\" /]

[fenway]

You'll need a subquery for this, because you can't group by first and then order by -- that is, you want to order by date desc. on unique objectid's:

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid = ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]

[tomato]

Thanks fenway,

I didn't know you could use subqueries, How ever I'm left with an error atating that the subquery returned more than 1 row. Could you point me in the proper direction again ;)

I've found a bit more info.

[i] If the subquery returns more than one row, error 1242 will occur. In that case, the query should be rewritten as:

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);[/i]

How ever this leaves me with original issue for some reason, as if the SELECT DISTINCT isn't functioning.

[fenway]

My bad -- I've not been thinking properly today, from the looks of my recent posts... I meant "IN", not "=":

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]

[tomato]

[!--quoteo(post=356789:date=Mar 20 2006, 06:11 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 20 2006, 06:11 PM) [snapback]356789[/snapback][/div][div class=\'quotemain\'][!--quotec--]
My bad -- I've not been thinking properly today, from the looks of my recent posts... I meant "IN", not "=":

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]
[/quote]

It parses fine although the select DISTINCT is not doing what it is supposed to do... Some how I'm still gettingt he same results. How is that possible?

[wickning1]

You need to use a subquery that finds the objectid with MAX(date) with a GROUP BY. I don't have the energy to write it up but I will try to later.

[tomato]

A generic query would surfice. I would like to see the structure as the code below has failed to produce any results from the query at all. HAVING max(date) = date returned all the entries again.



[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' GROUP BY objectid HAVING max(date) ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]

BINGO!

Thank you both for helping me find the way. My host is using 4.0.25.
It's run on the test server but not the site.

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' GROUP BY objectid HAVING max(date)= date ) ORDER BY DATE DESC LIMIT 0, $limit"); [/code]

[wickning1]

Yeah that's pretty close. It looks like there would be naming ambiguities with that, but if it's working, I guess it's ok.