Chris,
I've found a bug in the Forum_WhatsNewGetAll stored procedure. This procedure currently retrieves the last PostID from each Thread in the order lowest to highest, and then select ths first n (where the default n is 10). This has the result of returning the lowest/oldest 10 threads.
I think you need to retrieve the the PostID in order highest to lowest, and then select the first n.
I've highlighted my changes below. I've also changed the order by in the output select, to counteract the change in the temporary table retrieve.
I thought it might be worth adding in a date selection in the first retrieval so as to reduce the temporary table size (since with 10K threads on a forum, there would be 10K rows), but I stored procedure skills aren't up to it, I get date conversion errors.
Cheers
Roger
--------------------------------------------------------------------------------------------
CREATE procedure dbo.Forum_WhatsNewGetAll
(
@ModuleID int,
@NumberOfThread int,
@UserID int,
@FromDate datetime,
@ToDate datetime,
@Filter nvarchar(500)
)
AS
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
PostID int)
DECLARE @sql nvarchar(1000)
SELECT @sql = 'INSERT INTO #PageIndex (PostID)' +
' SELECT Max(PostID) As PostID' +
' FROM Forum_Posts, Forum_Threads, Forum_Forums, Forum_Groups (nolock) ' +
' WHERE ModuleID = ' + CONVERT(varchar, @ModuleID) +
' AND Forum_Groups.GroupID = Forum_Forums.GroupID' +
' AND Forum_Forums.ForumID = Forum_Threads.ForumID' +
' AND Forum_Threads.ThreadID = Forum_Posts.ThreadID' +
' AND Forum_Posts.IsApproved = 1' +
' AND Forum_Posts.IsLocked = 0' +
' AND (Forum_Threads.ForumID NOT IN (SELECT ForumID FROM Forum_PrivateForums) OR' +
' Forum_Threads.ForumID IN (SELECT ForumID FROM Forum_PrivateForums (nolock) ' +
' WHERE RoleID IN (SELECT RoleID FROM UserRoles (nolock) WHERE UserID =' + CONVERT(varchar, @UserID) +
')))' +
@Filter +
' GROUP BY Forum_Posts.ThreadID' +
' ORDER BY Max(Forum_Posts.PostID) DESC'
Print @sql
EXEC(@sql)
SELECT
G.ModuleID,
T.ForumID,
F.Name AS ForumName,
P1.ThreadID,
P1.PostID As LastPostID,
P2.UserID As StartedByUserID,
U2.Alias As StartedByAlias,
P2.Subject As StartedSubject,
P1.Subject As LastSubject,
P1.UserID As LastPostUserID,
U1.Alias As LastPostAlias,
P1.Body As LastPostBody,
P1.CreatedDate As LastCreatedDate
FROM
#PageIndex PageIndex (nolock), Forum_Posts P1 (nolock), Forum_Posts P2 (nolock), Forum_Threads T (nolock), Forum_Forums F (nolock), Forum_Groups G (nolock), Modules M (nolock), Forum_Users U1 (nolock), Forum_Users U2 (nolock)
WHERE
P1.PostID = PageIndex.PostID AND
P1.CreatedDate > @FromDate AND
P1.CreatedDate < @ToDate AND
P1.ThreadID = T.ThreadID AND
P1.ThreadID = P2.PostID AND
P1.UserID = U1.UserID AND
P2.UserID = U2.UserID AND
T.ForumID = F.ForumID AND
F.GroupID = G.GroupID AND
G.ModuleID = M.ModuleID AND
PageIndex.IndexID <= @NumberOfThread
ORDER BY
PageIndex.IndexID
GO