Welcome to WebmasterWorld Guest from 54.147.62.140

Forum Moderators: open

Message Too Old, No Replies

Confusion with mysql MAX

unexpected result

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 28, 2003
posts:869
votes: 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)

Junior Member

5+ Year Member

joined:May 11, 2009
posts:74
votes: 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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 28, 2003
posts:869
votes: 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)

Administrator

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

joined:Aug 10, 2004
posts:10550
votes: 10


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)
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members