SELECT kj_posts.ID, kj_posts.post_date, max( kj_comments.comment_ID ) , kj_posts.post_title
FROM kj_posts
LEFT  OUTER  JOIN kj_comments ON kj_comments.comment_post_id = kj_posts.ID
WHERE 1=1
GROUP  BY kj_comments.comment_post_id

GROUP BY kj_posts.ID, kj_posts.post_date,  kj_posts.post_title

Cheers! It's been a while since I've written any SQL, and it appears my head has forgotten some stuff.

The WHERE 1=1 thing is just a placeholder for when I put in the actual WHERE clause later. Because otherwise I'll forget.

SELECT kj_posts.ID, kj_posts.post_date, max( kj_comments.comment_ID), 
kj_posts.post_title FROM kj_posts LEFT  JOIN kj_comments ON 
kj_comments.comment_post_id = kj_posts.ID GROUP BY kj_posts.ID 
ORDER BY kj_posts.post_date

Standard SQL I would have though.

The primary key requires the column to be unique so grouping on other columns becomes redudant because there is no duplicate data.

I was under the impression that the query optimiser rather than scan the query to know how to eliminate the unrequired modifiers, it simply reorgansied the query internally so it was more efficient in how it opened and closed tables and read from keys and such like.

After all without running the query multiple times it wouldn't know if the modifiers made a difference or not to the results would it?

i don't really know what it does and doesn't do ... just that it uses clevers to recognise queries that can be better performed differently, without actually trying the different versions.

I was under the impression that it did things like notice that one of the items in a group by is a unique index, so would ignore the rest?

No idea how i got this idea though, so its most probably wrong.

SIGSIZESWITCHER