Post/Topic Query Problem. Stop query in a loop!

[m11oct]

I have TABLE1 with column A where A is a unique index. (Say topics on a forum, for example.)

I have TABLE2 with columns B, C and D where B is the unique index and C will refer to one of the rows in TABLE1. (Say posts on a forum, where C identifies to which topic the post belongs and D is the time of the post.)

I would like be able to...

For each row (value of A) in TABLE1, identify the row (value of B) in TABLE2 where C=A and D is greatest (i.e. greater than any other value of D where C=A).

I am currently using the following code but it is very slow:

$res=query("SELECT a FROM table1");
while ($row=mysql_fetch_row($res)) {
 $res2=query("SELECT b,c,d FROM table2 WHERE c=".$row[0]." ORDER BY d DESC");
 //the above will return one result which will be $result
 echo $row[0] . ';' . $result;
}

I would like to be able to just have one query and run through the loop without having to do another query in the loop.

Thank you

[fenway]

Sounds like you need a correlated subquery, where the inner query finds the group-wise maximum, and then the outer query simply goes through all the rows.

[m11oct]

Can you tell me what the query I would need would look like?

Thanks

[shoz]

This should be what you're looking for.
[code]
SELECT
t1.a, t2.b, t2.c, t2.d
FROM
table1 AS t1
INNER JOIN
table2 AS t2
ON t1.a = t2.c
INNER JOIN
(
    SELECT
    c, MAX(d) AS d
    FROM
    table2
    GROUP BY
    c
) AS t2max
ON
t2.c = t2max.c
AND
t2.d = t2max.d
[/code]

[fenway]

Or you could use a derived table, which is much better as far as performance is concerned, as shoz pointed out.