Selecting Maximum value

Dark_Storm

My current query is
$RQ = "SELECT max(id) AS id, Category, TName, Username, Started FROM forum GROUP BY Category DESC";

however whenever i run it through the while() loop to get the different values (using mysql_fetch_array() to hold them) it grabs the highest id in the forum, but then just grabs the rest of the information from the lowest id

Any help?

Oersoep

Mysql tells you the maxima, but doesn't connect the id to the row it takes the other info from. It just gets the other info from the beginning of the table... I guess you'll have to get the ID's and query for the rest of the information.

If you just want the complete row of the highest ID, I suggest this:

$RQ = "SELECT id, Category, TName, Username, Started FROM forum ORDER BY id desc limit 1";

But don't you know the category?
What exactly are you trying to do?
Do you want the latest addition of every category?
Or just the latest no matter the category?
Or the latest of a specific category?

@ any experts:
I must say I think this is strange behaviour...
Do all databases work this way?

HalfaBee

That is what your SQL asks for.

$RQ = "SELECT max(id) AS id, Category, TName, Username, Started FROM forum GROUP BY Category DESC";

You are asking for the max(id) and not specifying a WHERE, so you will always have the last Id and the first post.

I hate GROUP BY it only seems to return the first occurence of the item.

I think you will have to do a query for each category.

You may want to post this in the DB section, you may get a better response.

You could do the processing afterwards if you don't want to do too many queries.
Some hosts have limits on these.

<?
$RQ = "SELECT  id, Category, TName, Username, Started FROM forum ORDER BY ID  DESC";
$cats = array( 'cat1','cat2','cat3' );

while( (($row=mysql_fetch_array($RQ))!==FALSE) and (count($cats)>0) )
{
    if( in_array( $row['Category'] , $cats ) )
    {
        $lastposts[] = $row;
        unset( $cats[$row['Category']] );
    }
}

?>

This should search thru the results and only stop when all the categorys have been found or the end of the data.

$lastposts should have the 3 last posts.

HalfaBee

Just found a better answer

$RQ = "SELECT id, Category, TName, Username, Started FROM forum ORDER BY Category,id DESC"

This returns the list sorted by Category then ID, so the first rows returned will be the highest Id.
You just need to fetch the first count(Categorys) to get the results.

HalfaBee

Dark_Storm

Thanks. That works. I just had to get rid of the DESC because it was giving me ALL values from the first query. ITs good now, lol and it showed another flaw in my code but i got that fixed.
And I knew i could do it the other way, but that is more code, more processing time, and this single-query might come in far more handy mulitiple querys could in some future situation.
Thanks again.