[SOLVED] Can this be done without using subqueries?

[artacus]

I have a table that has a student's reading fluency scores. Students will be tested multiple times thruout the year and with no set schedule. (One student may be tested 2x a year, another weekly)

So I need to get the most recent score for each student. I've been doing it using sub-queries (get max(test_date) for each student). But now I have a case where I need to do it w/o using subqueries.

[code]
SELECT studentID, @mtd:=MAX(test_date), MAX(IF(test_date = @mtd, score,0)) AS score,
FROM reading_fluency AS rf
WHERE test_date > '2006-09-01'
GROUP BY studentID
[/code]
Doesn't work because @mtd ends up being set as the MAX of all students, not the student we are currently working with. BTW MAX(test_date) when run w/o assigning it to a variable DOES select the max for that student.

[code]
SELECT studentID, SUM(IF(test_date = MAX(test_date), score,0)) AS score,
FROM reading_fluency AS rf
WHERE test_date > '2006-09-01'
GROUP BY studentID
[/code]
Doesn't work, I get "invalid use of group function" ... the MAX() inside the SUM()

Can it be done?

[btherl]

Hmm.. I can offer a low-tech solution.  Select all the test results and [code=php:0]ORDER BY studentID, test_date DESC[/code].  Then use a foreach loop to pick the first entry for each student.  You can also order it ASC and pick the last entry (that can be convenient, since you just keep assigning.. and the most recent assignment for that student is their latest test score).

I'm very surprised that the max ends up being for all students.  I guess it's a side-effect of how assignment is implemented.

Anyway, my answer to your original question is "No, I can't think of how to do this without subqueries".  It's breaking the rules to aggregate one column and then use that result during the aggregation of another column.

[fenway]

You could always use a temporary table instead of a subquery.

[artacus]

Well the problem is that I have a bunch of assessments (about  12 test groups, 100 tests & 800 test strands) and I am trying to map these so I can find out where each piece of information is located in the database and then generate statistics and averages for the district, schools and classes.

Most of the tests occur at a given time (1st, 2nd, 3rd trimester) so its easy to pull that information. But because reading fluency is given year round, I'm having a hard time mapping it. If there were a work around to get @mtd to work, it would have fit in with all of my other assessments but  now I'll have to calculate the stats w/o mapping them.

[btherl]

What is the reason that you can't use a temporary table?  What fenway means is something like this (my syntax may not be correct for mysql):

[code=php:0]CREATE TEMP TABLE student_max_test_date AS SELECT studentID, MAX(test_date) AS test_date
FROM reading_fluency
WHERE test_date > '2006-09-01'
GROUP BY studentID;[/code]

[code=php:0]SELECT studentID, test_date, score
FROM reading_fluency
JOIN student_max_test_date USING (studentID, test_date)[/code]


There's no need for the awkward construct for score here, as the max test_date has already been found.  Then a simple join gets you the scores that match those max test_dates.

[artacus]

Ok, it might work.  If I build the temp table before I run the stats, I think I'd be able to map this. Thanks guys.