Lets assume we are looking for grades of user with id=1 (just to test query without php).
You can replace it with '$Id' later.
If your date column is precise enough to ensure it is unique for every grade you can use:
SELECT s.section_name, g1.grade FROM
( SELECT g.section_id, max(g.date) AS last_grade_date
FROM grades g
WHERE g.user_id = 1
GROUP BY g.section_id ) gd
JOIN grades g1
ON g1.date = gd.last_grade_date
JOIN sections s
ON s.id = gd.section_id
If your date is not unique then you have to join grades back to itself on id found in dependent subquery:
SELECT s.section_name, ga.grade FROM
( SELECT g1.section_id, max(g1.date) AS last_grade_date
FROM grades g1
WHERE g1.user_id = 1
GROUP BY g1.section_id ) gmax
JOIN grades ga
ON ga.id =
( SELECT g2.id
FROM grades g2
WHERE g2.user_id = 1
AND g2.section_id = gmax.section_id
AND g2.date = gmax.last_grade_date
LIMIT 1 )
JOIN sections s
ON s.id = gmax.section_id
You need index on (user_id, section_id, date) for this query.
ALTER TABLE grades ADD INDEX user_section_date( user_id, section_id, date ) ;
@comment:
Ok I'll try to explain the second query as it gives correct results for any case.
In table g1 we take rows from grades for user with id=1, we group them by section and in every section we find a maximum date - which means most recent date. At this point we don't know yet which row exactly holds this most recent date, because we can select only columns that are in the group by or aggregate functions (like max()). Mysql allows to select other columns, like grade, called hidden columns (other dbs would just throw a syntax error), but is free to return any row from each group, usually not the one we want, and we want the one holding most recent date. If all rows have the same value, like user_id in this case, its ok, but we need grades, which can be different in every group. For small tables select can return the right one, thats why some people claim ordering by date can help, because they test it on small tables and see correct results, but it goes wrong once the table grows, there are row updates, deletes and so on.
Basically we now have a list of sections and most recent dates and we need to find out grades. So we have to join this g1 table we just got to the grades table to find a row that holds the most recent date for every section. Id is the only column we are sure to be unique (if we don't join on unique column or unique list of columns we'll get more rows and we want exactly one) so we try to find this id in dependent subquery g2 (its a subquery that references values from outside, from gmax in this case, which is just an alias for g1, explained earlier).
Once we have grade for every section_id the only thing left to do is to join that to sections table on section_id to get section_name instead of its id value.
