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.