mySQL/GROUP BY problems

ChrisB posted this at 15:37 — 16th August 2000.

They have: 9 posts

Joined: May 2000

Hi,
I'm implementing a forum system in mySQL. On the page that lists all the threads, I want to display the details [date & user] of the last post for every thread.

simplified, it looks like this right now:

SELECT t.ThreadID,p.Date as LastPostDate,p.MemberID as LastPostBy FROM Threads t, Posts p WHERE p.ThreadID=t.ThreadID GROUP BY t.ThreadID

This is basically what I want, but of course it returns the first post's date of every thread, not the last one.

My idea was to reverse order by Date within the grouping, but the [my]SQL ordering only works AFTER the grouping, so it doesn't have any effect.
Using MAX() in the SELECT line, I could find out the date of the last post, but how can I find out the Member's details?

Any ideas?

Thanks!

Christian Becker

anti posted this at 16:05 — 16th August 2000.

They have: 453 posts

Joined: Jan 1999

This looks so familiar ...

My solution was to do it manually:

#pseudocode

$newest = '0000-00-00 00:00:00';
$posts = query( 'SELECT p.id, p.date FROM threads t, posts p WHERE p.threadid = t.id' );
while( $post = fetchrow( $posts ) ){
  if( $post[ 2 ]>$newest ){
    $newest = $post[ 1 ];
    $newestID = $post[ 0 ];
  }
}

query( 'SELECT login FROM posts WHERE id='.$newestID );

Obviously there is some errorchecking involved,
but that should do it.

If you want to see my (working) php version in action take a look at
http://www.biker-forum.com/whf/
btw:
That will be GPLed as soon as I cleaned up the code and fixed some security holes.

anti

ChrisB posted this at 17:16 — 16th August 2000.

They have: 9 posts

Joined: May 2000

Hi,
Since this is a high traffic site, I would prefer a single SQL query; doing such sub-queries for all 50 threads per page is way too much... any other ideas? =)

Thanks anyway!

Christian Becker

Randall posted this at 06:06 — 21st August 2000.

They have: 141 posts

Joined: Aug 1999

Why don't you try sorting them by DESC? Just an idea, thats how I do it on my forum (nobody uses it, talkback is more popular these days). ( http://dlo.net )

ChrisB posted this at 07:23 — 21st August 2000.

They have: 9 posts

Joined: May 2000

... because I can't ORDER BY (desc) within the GROUP BY statement, that's exactly my problem. Or do you know how that would be possible?

Thanks,

Christian Becker