Jump to content


Photo

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


  • Please log in to reply
4 replies to this topic

#1 m11oct

m11oct
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 14 September 2006 - 08:02 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 September 2006 - 08:12 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 m11oct

m11oct
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 14 September 2006 - 09:11 PM

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

Thanks

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 14 September 2006 - 11:21 PM

This should be what you're looking for.
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


#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 September 2006 - 12:31 PM

Or you could use a derived table, which is much better as far as performance is concerned, as shoz pointed out.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users