Matthias, thank you for your prompt response!

Good news: I have been playing around with this issue and have come up with a fix that works fine in my case:

Regarding the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." in the Forum Module v03.20.09.

The original solution (which is yellow and green higlighted in the script below) was proposed by John Liptak in:
http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/7/threadid/50943/scope/posts/threadpage/7/Default.aspx
The issue (and solution) posted in GEMINI by Alex Shirley is:
http://support.dotnetnuke.com/issue/ViewIssue.aspx?ID=3690&PROJID=7

The solution I have found for those who cannot (or just don't want to) connect directly to the database:

1. Open the web.config file and search for the Connection String to the Database

2. Copy the values of the databaseOwner and objectQualifier fields. They are typically the following:
 - databaseOwner = "dbo"
 - objectQualifier = "" or "dnn_"

3. Replace those values in the script at the end of the post
 - i.e. "{databaseOwner}" replaced by "dbo" and the same with "{objectQualifier}", do not use the quotes.

 for example, the first line of the script should look like:
   ALTER PROCEDURE dbo.Forum_SearchGetResults
     or
   ALTER PROCEDURE dbo.dnn_Forum_SearchGetResults
     or your own one.

4. From your DNN application, under Host-SQL menu:
     a) Paste the modified script
     b) Mark "Run as Script"
     c) Click on "Execute"
     d) Wait for the "The Query completed successfully!" message.

5. Check that the error on the Forum Module is gone, by clicking the "My Posts" button.

Saludos,
Alejandro.

---------------------------------------
ALTER PROCEDURE {databaseOwner}.{objectQualifier}Forum_SearchGetResults
(
@Filter  nvarchar(500),
@PageIndex int,
@PageSize int,
@UserID  int,
@ModuleID int,
@FromDate datetime,
@ToDate datetime
)
AS
 -- Create a temp table to store the select results
 CREATE TABLE #PageIndex
 (
  IndexID int IDENTITY (1, 1) NOT NULL,
  ThreadID int
 )
 -- Create dynamic SQL to populate temporary table
 DECLARE @sql nvarchar(2000)
 SELECT  @sql = 'INSERT INTO #PageIndex(ThreadID) SELECT DISTINCT(P.ThreadID) CreatedDate ' +  
    'FROM {objectQualifier}Forum_Posts P ' +
    'INNER JOIN {objectQualifier}Forum_Threads T ON P.ThreadID = T.ThreadID ' +
    'INNER JOIN {objectQualifier}Forum_Forums F ON T.ForumID = F.ForumID ' +
    'INNER JOIN {objectQualifier}Forum_Groups G ON F.GroupID = G.GroupID ' +  
    'INNER JOIN {objectQualifier}Forum_Posts LP ON T.ThreadID = LP.ThreadID ' +
    'WHERE P.ThreadID = T.ThreadID AND ' +
    'T.ForumID = F.ForumID AND ' +  
    'F.GroupID = G.GroupID ' + 
    'AND P.[CreatedDate] >  ''' + CONVERT(varchar, @FromDate) + ''' ' +
    'AND P.[CreatedDate] < ''' + CONVERT(varchar, @ToDate) + ''' ' +
    'AND P.IsApproved = 1 ' +
    'AND P.IsLocked = 0 ' +
    'AND G.ModuleID = ' + CONVERT(varchar, @ModuleID) + ' AND ' +
  
    '( F.ForumID NOT IN (SELECT ForumID FROM  {objectQualifier}Forum_PrivateForums) OR ' +
     ' F.ForumID IN ( ' +
      'SELECT ForumID  ' +
      'FROM  {objectQualifier}Forum_PrivateForums (nolock)  ' +
      'WHERE (RoleID IN (SELECT RoleID FROM  {objectQualifier}UserRoles WHERE UserID = ' + CONVERT(varchar, @UserID) + ' ) ' +
      'OR EXISTS (SELECT TOP 1 1 FROM  {objectQualifier}Users WHERE UserID = ' + CONVERT(varchar, @UserID) + '  and IsSuperUser=1))))  ' +
    @Filter + ' GROUP BY P.ThreadID , P.CreatedDate ORDER BY P.ThreadID DESC '
 Print @sql
 EXEC(@sql)
 -- All of the rows are inserted into the table - now select the correct subset
 DECLARE @PageLowerBound int
 DECLARE @PageUpperBound int
 DECLARE @RecordCount int
 -- Set the page bounds
 SET @PageLowerBound = @PageSize * @PageIndex
 SET @PageUpperBound = @PageLowerBound + @PageSize + 1
 SELECT @RecordCount = COUNT(*) FROM #PageIndex

 Print @RecordCount
 -- Select the data out of the temporary table
 -- Select the data out of the temporary table
 SELECT
  PageIndex.ThreadID, 
  P.[Subject],
  P.[CreatedDate],
  FU.[UserID],
  FU.[Alias],
  T.[ForumID],
  T.[Replies],
  T.[Views],
  F.[Name] As ForumName,
  RecordCount = @RecordCount,
  (SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating  WHERE ThreadID = PageIndex.ThreadID) As RatingCount,
  (SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating  WHERE ThreadID = PageIndex.ThreadID) As Rating,
  (SELECT Distinct  Count(UserID) FROM {objectQualifier}Forum_ThreadsRead WHERE ForumID = T.[ForumID] AND UserID = FU.[UserID] AND P.[CreatedDate] > LastPurseDate AND ReadThreads NOT LIKE '%' + CONVERT(varchar, PageIndex.ThreadID) + ';%')  As IsUnRead
 FROM
  {objectQualifier}Forum_Posts P
  INNER JOIN {objectQualifier}Forum_Threads T ON P.ThreadID = T.ThreadID
  INNER JOIN {objectQualifier}Forum_Forums F ON T.ForumID = F.ForumID
  INNER JOIN {objectQualifier}Forum_Users FU ON P.UserID = FU.UserID, #PageIndex PageIndex
 WHERE
  PageIndex.IndexID > @PageLowerBound AND
  PageIndex.IndexID < @PageUpperBound AND
  PageIndex.ThreadID = P.ThreadID AND 
  FU.[UserID] = P.[UserID] AND
  P.[ThreadID] = T.[ThreadID] AND
  P.[CreatedDate] > @FromDate AND
  P.[CreatedDate] < @ToDate AND
  T.[ForumID] = F.[ForumID] AND
  (P.[CreatedDate] = (SELECT MAX([CreatedDate]) FROM {objectQualifier}Forum_Posts WHERE ThreadID = PageIndex.ThreadID)) AND
  (F.ForumID NOT IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums) OR
  F.ForumID IN (
   SELECT ForumID
   FROM {objectQualifier}Forum_PrivateForums
   WHERE (RoleID IN (SELECT RoleID FROM {objectQualifier}UserRoles WHERE UserID = @UserID)
   OR EXISTS (SELECT TOP 1 1 FROM {objectQualifier}Users WHERE UserID = @UserID and IsSuperUser=1))))  
 ORDER BY
  P.CreatedDate DESC
---------------------------------------