I am using MySQL 4.0.21 trying to write a query that will return a row id value in combination with aggregate functions.

Goal: Given the sri_student_lexile_history table (see below), return the last quiz for each student with the result set ordered by lexile.

The fields of interest are the id (key), user_id, time_taken (a unix time stamp) and lexile.

#----------------------------
# Table structure for sri_student_lexile_history
#----------------------------
CREATE TABLE `sri_student_lexile_history` (
`ID` int(8) NOT NULL auto_increment,
`LAST_NAME` varchar(32) NOT NULL default '',
`FIRST_NAME` varchar(32) NOT NULL default '',
`GRADE` tinyint(2) NOT NULL default '0',
`MODIFIED_SEQ_PREV_ID` int(8) default NULL,
`VALID` char(1) NOT NULL default '1',
`USER_ID` varchar(32) NOT NULL default '',
`LEXILE` smallint(6) NOT NULL default '0',
`TIME_TAKEN` bigint(6) NOT NULL default '0',
`TIME_INSERTED` bigint(8) NOT NULL default '0',
`QUIZ_SEQ` int(4) default NULL,
`SAM_USER_ID` varchar(32) default NULL,
`TEST_NAME` varchar(32) NOT NULL default '',
PRIMARY KEY (`ID`)
) TYPE=InnoDB COMMENT='SRI Student Lexile History table.';
#----------------------------
# Records for table sri_student_lexile_history
#----------------------------

insert into sri_student_lexile_history values
(1, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 814, 1096459679672, 0, null, null, 'test1'),
(2, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 872, 1096462387299, 0, null, null, 'test2'),
(3, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 834, 1096471690694, 0, null, null, 'test3'),
(4, 'Martin', 'Peter', 0, null, '1', 'sriStudent02', 800, 1096471690694, 0, null, null, 'test1'),
(5, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 135, 1096472787715, 0, null, null, 'test4');

This is the only way I can think of doing it is using two separate queries with some code in the middle:

Starting with:

ID USER_ID LEXILE TIME_TAKEN
1 sriStudent01 814 1096459679672
2 sriStudent01 872 1096462387299
3 sriStudent01 834 1096471690694
5 sriStudent02 800 1096471690694
4 sriStudent01 135 1096472787715

1) select * from sri_student_lexile_history ORDER BY user_id, time_taken DESC;

This gives me:

ID USER_ID LEXILE TIME_TAKEN
4 sriStudent01 135 1096472787715
3 sriStudent01 834 1096471690694
2 sriStudent01 872 1096462387299
1 sriStudent01 814 1096459679672
5 sriStudent02 800 1096471690694

Now all of the userIDs are grouped together, and the 1st one is always the latest test. So I go through the result set and grab the ID for each result set where the userID changes. In this case, 4 and 5.

Then I issue the select:

SELECT * FROM sri_student_lexile_history WHERE id IN (4,5) ORDER BY lexile DESC;

Which gives me the final result.

ID USER_ID LEXILE TIME_TAKEN
5 sriStudent02 800 1096471690694
4 sriStudent01 135 1096472787715

Is it at all possible to write just one query using MySQL 4.0.21?

If I don't have to get the row id my following query works just fine:
select user_id,min(lexile) as lexile,max(time_taken) as last_quiz_taken_time
from sri_student_lexile_history
group by user_id
order by lexile desc;

Thank you much in advance -

Noga