[SOLVED] problem with MAX

[asmith]

Hi,

 

Using mysql 5.0,

I have a simple table like this :

 

CREATE TABLE records (
  id int(11) NOT NULL auto_increment,
  member varchar(250) NOT NULL,
  option1 varchar(30) NOT NULL,
  option2 varchar(30) NOT NULL,
  option3 varchar(30) NOT NULL,
  points int(11) NOT NULL default '0',
  time int(11) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

 

 

I'm trying to run this query on this :

 

select member,max(points),time from records where option1= 'competition1' and option2 = 1 and option3 = 'l'  group by time

so that it gives me the highest point in each "time".

 

 

The max points of the result is correct according to the time, the only mistake is the member field. it is not giving me the member which had the max points, it is just giving me another member who hasn't the max.

 

So that I'm having the true value of max in each "time" but with the wrong member.

 

Why?

[KPH71]

The thing that you have to remember is that this query will get more than one row. Each time the member and time fields will be outputted and then you will have an extra value which will be the max number of points for the entire table - which will always be the same.

 

I would suggest something like putting the max(points) in the WHERE clause. So you tell it to get it WHERE points=max(points) or something similar. This way it will get the row(s) where the points value is the maximum.

[asmith]

I tried that, It gives me "Invalid use of group function"

I even tried  group by time HAVING points = max(points) and it return no result.

 

:/

[KPH71]

Can you post the entire query - as it is unlikely that this is caused by the points = max(points)

[asmith]

Have you tried it yourself?

That's my query.

[fenway]

You can't just randomly pick whatever columns you want when using group by -- member isn't a "valid" option here.

 

You'll need to join back to the table once you're found the "max" record to get the remaining details.

[asmith]

You'll need to join back to the table once you're found the "max" record to get the remaining details.

 

By a second query?

 

I tired  :

select r1.member,max(r1.points),r1.time from records as r1 inner join records as r2 on max(r1.points) = r2.points where r1.option1= 'competition1' and r1.option2 = 1 and r1.option3 = 'l'  group by r1.time

 

gives me invalid use of group function.

[fenway]

See here and here.

[asmith]

wow lol never know it was this much common

 

Thanks for the posts and please accept my apology for repeated question ^^