Selecting multiple rows based on maxvalue

Dark_Storm

Hey all. I have my little forum. It is set up as (this is a stripped down version, but its all thats relevant so i keep it simple)

Now obviously this is going to have a lot more entries in it, its a forum. But for simplicities sake I will keep it...simple
Anyway. What I want to do is select the biggest 'id' from table from each category, but do it all in one query. I don't need the entire row, just the single 'id' value.
I did a search for any posts relevant to this, but nothing helped me, nothing even was over what I am wondering now.
On my forum there are about 9 categories and I already tried to do a separate query for each one and run it thru a while() loop to grab the variable but that don't work, it exceeds the server's 3 second limit. So I need it all done in one little query.
Using the table above, can anyone help?

mahers

Is this what you mean.

SELECT max(id) AS id FROM forum GROUP BY category

Dark_Storm

That looks as if it could work. But how would I get the value out of it?

$query = "SELECT max(id) AS id FROM forum GROUP BY category";

while ($Row = mysql_fetch_array(mysql_query($query)))
{
      $Variable1 = $Row[Arrive][id];
      $Variable2 = $Row[Leave][id];
}

Something like that?

diego25

You can't just retrieve the ID, coz otherwise you won't know which ID is for which category..

Try

<?
$query = "SELECT max(id) as id, category AS id FROM forum GROUP BY category";

$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
  print $row['category'] . ':' . $row['id'];
}
?>

And please plase do not ever use this:
while ($Row = mysql_fetch_array(mysql_query($query)))
The reason is coz if you do, then it will execute the query every loop, and thus slowing down your script.

Diego