sorting troubles

[morrisoner]

I have a search page that displays notes. It lists them according to their category. ie. it will list a subject, then under subject all the topics in that subject, then the notes.

I am having trouble sorting the subjects and topics. Because there will be multiple values for the date in each topic because there are several notes for each topic.

Does anyone know how I can do this?

[daveyboy]

you could sort the results into a tree from the bottom up. start by sorting the items by date in all the sections, then sort the sections by the date of their top item.

[morrisoner]

i tried this query

SELECT DISTINCT notes.topic, max(date) FROM notes LEFT JOIN subjects ON notes.subjectID = subjects.subjectID WHERE 1 AND notes.subjectID=6 GROUP BY topic ORDER BY date DESC

but it doesnt seem to order them properly

I also tried this but i get an error

SELECT DISTINCT notes.topic FROM notes LEFT JOIN subjects ON notes.subjectID = subjects.subjectID WHERE 1 AND notes.subjectID=6 GROUP BY topic ORDER BY max(date)