homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Confusion with mysql MAX
unexpected result

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

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)

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)

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)

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)

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved