Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spudhead
Starting Member
34 Posts |
 Posted - 2002-07-22 : 07:28:58
|
| I've got a forum running off two basic tables: forum_threads and forum_posts:forum_threads columns: id subjectforum_posts columns: id thread_id post_date bodyI've got the following script, that creates a list of forum threads ordered by which threads were most recently added to (ie: which enrty in the forum_posts table has the most recent date). If that makes sense:SELECT TOP 10 CONVERT(varchar(20 ), forum_posts.post_date) AS last_post_date, forum_threads.id, forum_threads.subjectFROM forum_threads INNER JOIN forum_posts ON forum_posts.thread_id = forum_threads.idORDER BY forum_posts.post_date DESCTrouble is, if there's more than one entry on the forum_posts table with a particular thread_id, then it pulls out all of 'em. What I want it to do is pull out the top 10 recently-added-to threads but only those with distinct thread_id's.I tried sticking DISTINCT in front of forum_threads.id, but that threw an error.Can anyone point me in the right direction? Many thanks. |
|
|
rksingh024
Yak Posting Veteran
56 Posts |
Posted - 2002-07-22 : 08:07:31
|
| SELECT TOP 10 CONVERT(varchar(20 ), forum_posts.post_date) AS last_post_date, forum_threads.id, forum_threads.subject FROM forum_threads INNER JOIN forum_posts ON forum_posts.thread_id = forum_threads.id ----where post_date = (select max(post_date) from forum_posts fp2 where fp2.thread_id = forum_posts.thread_id)----ORDER BY forum_posts.post_date DESC Ramesh |
 |
|
|
|
|
|
simple SQL teaser