Confusion with mysql MAX

unexpected result

[HelenDev]

I want to select a row which has the highest ID and matches the id from another table, so I figured MAX was the way to go here. My query is like this:

$prog_template_query = 'SELECT MAX(id), prog_id, acad_year, pdf, pdf_euro FROM prog_templates WHERE prog_id='.$prog_row["id"].' GROUP BY prog_id;';

The row I would expect it to return is the one which has the highest ID:

Array ( [0] => 192 [MAX(id)] => 192 [1] => 6 [prog_id] => 6 [2] => 2009 [acad_year] => 2009 [3] => 1 [pdf] => 1 [4] => 1 [pdf_euro] => 1 ) 

but instead it seems to be picking out the highest ID, but the year from another (older) row altogether, so it is a mixture of the two!

Array ( [0] => 192 [MAX(id)] => 192 [1] => 6 [prog_id] => 6 [2] => 2006 [acad_year] => 2006 [3] => 1 [pdf] => 1 [4] => 1 [pdf_euro] => 1 ) 

Can anyone help me here? Am I totally misunderstanding the way MAX works?

I'm afraid I have no idea what the GROUP BY bit is for either, but I can see that changing it to another field changes the results again!

[abidshahzad4u]

It it's auto incremented id then you try to get this like

select id from table order by id desc limit 1

I think this will solve your problem if the case is above

[HelenDev]

Ahhh, thank you!

I was obviously thinking about that one waaaay too much LOL!

If anyone does want to offer an explanation of exactly what MAX and GROUP BY are for, and why they would mix up the content of two different rows in a result, I'd still be interested to hear it!

[phranque]

this may have the solution you are looking for:
MySQL :: MySQL 6.0 Reference Manual :: 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field [dev.mysql.com]
(using a subquery)