problems sorting/displaying query results

[mnp13]

I am trying to write the SQL for a forum, I have one table with the forum topics and another for the forum posts. I need to create the 'front page' listing that will show all of the topics and the date of the most recent post for each one. Currently I have 5 topics and 16 posts, I need a result set of the last post for each topic. Currently, I have only succeeded in returning all 16 rows sorted by date, but I can't use that. Eventually there will be well over 50 topics and a few hundred posts, so I have to make sure to only return one record (the most recent) for every topic.

I've been trying 'distinct' and outer and inner joins and MAX and everything else I can think of, but have yet to hit it.

I have this feeling that it is pretty simple and the answer is staring me in the face..... And of course this was due on Tuesday *groan*

Thanks for the help!!!
Michelle

(that should be 'novice programmer' *grin*)

 

[SQLBill]

Knowing something about the structure of your database would help - table names, column names, primary/foreign keys that are part of your query.

But, here's a stab at it....


SELECT t1.topic, t2.post
FROM topictable t1, posttable t2
WHERE t1.pk = t2.fk
AND t2.date = (select max(t2.date) from t2 where t1.pk = t2.fk)

If I'm right, that should the inner select should pull the MAX date from all the posts for a specific topic.

Then the outer select should pull the topic and post based on the posts belonging to the topic and the date of the post matching the MAX date.

-SQLBill

 

[mnp13]

Sorry about not being clear... your solution worked perfictly (as soon as I found the typo that was giving me an error!)



Thanks for your help!

Michelle