Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Confusion with mysql MAX

unexpected result



9:31 am on May 14, 2009 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

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!


1:29 pm on May 14, 2009 (gmt 0)

5+ Year Member

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


3:45 pm on May 14, 2009 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

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!


9:35 am on May 24, 2009 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

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)

Featured Threads

Hot Threads This Week

Hot Threads This Month