Jump to content

Archived

This topic is now archived and is closed to further replies.

m11oct

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Or you could use a derived table, which is much better as far as performance is concerned, as shoz pointed out.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.