Order or Max?

[rarebit]

Hi, I have this statement:

$s = "SELECT ".$tnF.".id, ".$tnF.".title, ".$tnF.".description, MAX(updated) AS updated, MAX(last_poster) AS last_poster , COUNT(*) AS num 
FROM ".$tnF." 
LEFT JOIN ".$tnT." ON forum_id = ".$tnF.".id 
WHERE cat_id = '".$e['id']."' 
GROUP BY ".$tnF.".id 
ORDER BY ".$tnF.".id ";

 

However the 'MAX(last_poster)' returns the name which is largest numerically, but I need it to be the name associated with the 'MAX(updated)' row.

 

Any ideas...

[fenway]

Then you need to join again.

[rarebit]

i've tried numerous ways to join them (there from the same table) but to no avail:

$s = "SELECT ".$tnF.".id, ".$tnF.".title, ".$tnF.".description, MAX(aa.updated) AS updated, bb.last_poster, COUNT(*) AS num 
FROM ".$tnF." 
INNER JOIN ".$tnT." AS aa ON ".$tnF.".id = aa.forum_id 
LEFT JOIN  ".$tnT." AS bb ON aa.updated = bb.last_poster 
WHERE cat_id = '".$e['id']."' 
GROUP BY ".$tnF.".id 
ORDER BY ".$tnF.".id ";

 

some form of example would help, i've gone through most of these...

 

[fenway]

No, you have to join it AFTER that query, since you're using GROUP BY -- take that entire query, make it a derived table, and join it again.

 

Wait a second, maybe I didn't understand your original question?