Michal Stankoviansky wrote:
> Could you be more specific please? Nothing I tried worked.

You want to put the WHERE clauses in the joins -- that is where you are
selecting the max, within each group, not at the very end (which makes
it global).



>
> I tried this:
>
> SELECT
> b1.id,
> bt.topic_subject,
> b1.post_text,
> b1.time_posted,
> u1.username,
> b2.id,
> b2.post_text,
> u2.username,
> b2.time_posted,
> b2.answer_to
> FROM board b1
> JOIN board_topics bt
> ON b1.id = bt.id
> JOIN users u1
> ON b1.user_id = u1.id
> LEFT JOIN board b2
> ON b2.answer_to = b1.id
> LEFT JOIN users u2
> ON b2.user_id = u2.id
> WHERE b2.time_posted = MAX(b2.time_posted) /* <--- added this line */
> GROUP BY b1.id
> ORDER BY b2.time_posted DESC, b2.id DESC
>
> ...but that gives an error #1111: Invalid use of group function, I
> think because one can't use grouping functions in the WHERE clause.
>
>
> I also tried adding HAVING clause:
>
> SELECT
> b1.id,
> bt.topic_subject,
> b1.post_text,
> b1.time_posted,
> u1.username,
> b2.id,
> b2.post_text,
> u2.username,
> b2.time_posted,
> b2.answer_to
> FROM board b1
> JOIN board_topics bt
> ON b1.id = bt.id
> JOIN users u1
> ON b1.user_id = u1.id
> LEFT JOIN board b2
> ON b2.answer_to = b1.id
> LEFT JOIN users u2
> ON b2.user_id = u2.id
> GROUP BY b1.id
> HAVING b2.time_posted = MAX(b2.time_posted) /* <--- added this line */
> ORDER BY b2.time_posted DESC, b2.id DESC
>
> Which only returned one row...which, again, is not correct:
> http://www.metallica2.com/stuff/query03.html
>
> Any ideas? Thank you.
>
> --
> Michal Stankoviansky
>