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 |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2001-05-09 : 01:29:13
|
| I'm in the process of creating a messageboard system in that oh so trendy UBBesque fashion. Anyways. I'm trying to get the thread listings to sort properly. But they see it another way. Now I can get the threadId, and the poster, and the time of the first post no problem. However the sort order can't just be done from the posts with postLevel = 1 because when a reply is made to a thread, it needs to jump to the top of the thread list. I can't seem to figure out how to do this, because that would mean that I would need the largest(newest) postTime(dateTime field) value for each specific thread.I have the following(it runs in an SP), the temp table is queried later on to just retrieve the records for the page that's needed:CREATE TABLE #tempList ( tempId int IDENTITY, threadId int, threadStarter varchar(30), threadStarted dateTime )DECLARE @listAds AS varchar(1000)-- Insert from the classifieds table to the temp tableSET @listAds = "INSERT INTO #tempList (threadId, threadStarter, threadStarted) " +"SELECT MBPosts.threadId, users.username, MBPosts.postTime " +"FROM MBPosts, users " +"WHERE MBPosts.forumId = " + @forumId + " AND postLevel = 1 AND MBPosts.posterId = users.userId " +"ORDER BY MBPost.postTime DESC"The table MBPosts is structured as such:postId int IDENTITYthreadId intforumId intparentId intposterId intpostLevel intsortOrder intpostTime dateTimeposterIP varchar(20)postLocked bitsubject varchar(50)postText text |
|
|
|
|
|
|
|
Agh! It's always something isn't it?