Distinct records ordered by date

[andyede]

I have the basis of a forum similar in workings to this one. I am using perl with MySQL the problem i have is a simple SQL one.

I have a table storing all post data including fields.
forumID,
threadID,
postID,
postTitle,
datePosted,
timePosted

I want to list the threads in the order of last posted/replied to. So similar to this you click on the forum you want view and you get a list of the threads in that forum with the most recently active at the top. I don't mind how much data i can get for each thread, i can handle just getting the threadID and then i can loop back through it again and get the specific data for that thread (infact this would be preferable as it would give me more control although would work the server alittle harder).

the closest SQL i have to working is

SELECT DISTINCT threadID FROM postdata WHERE forumID='$fid' ORDER BY dataPosted, timePosted DESC;

but the order seems not to change

SQL is not my best subject, any ideas welcome

[Kiwi]

Does this work in mySQL?

PHP Code:

SELECT a.threadID MAX(a.dataPosted) as PostDate
FROM postdata a
WHERE b.forumID='$fid'
GROUP BY a.threadID
ORDER BY PostDate; 


[andyede]

no that just brings up an error. i've given up trying to so it in SQL. i just get an ordered list of thread IDs and remove the duplicate items in perl. i think each thread has to make about 5 database queries to get all the information now

[Julian Turner]

SELECT a.threadID MAX(a.dataPosted) as PostDate
FROM postdata a
WHERE b.forumID='$fid'
GROUP BY a.threadID
ORDER BY PostDate;

Should that not be

WHERE a.forumID='$fid'