CODEWITHSUNDEEP
sqlsql-servert-sqljoingroup-by
user12505227
user12505227

Reputation:

forum hot topics sql sort by date then replies

I don't know if this can be done with SQL only. I currently have SQL for latest topics for all forums. I need a hot topics version of this.

The order would be 20 topics from today with the most replies sorted by most replies at the top. Then the same for yesterday and the day before etc etc until a maximum of 2000 topics are retrieved. I have no idea how or if this can be done

BTW this is for SQL Server.

Today

replies 100
replies 80
replies 70
etc

Yesterday

replies 100
replies 80
replies 70
etc

and so on with a max of 2000 total topics

Here's the current latest topics SQL:

Stored procedure definition:

 CREATE PROCEDURE [dbo].[SPTopicsGetHot]
     @UserGroupId int = NULL
 AS
     /*
     Gets the latest messages in all forums  
     */
     SELECT TOP 200
         T.TopicId
         ,T.TopicTitle
         ,T.TopicShortName
         ,T.TopicDescription
         ,T.TopicCreationDate
         ,T.TopicViews
         ,T.TopicReplies
         ,T.UserId
         ,T.TopicIsClose
         ,T.TopicOrder
         ,T.LastMessageId
         ,T.UserName
         ,T.ForumId
         ,M.MessageCreationDate
         ,M.UserId AS MessageUserId
         ,MU.UserName AS MessageUserName
         ,F.ForumName
         ,F.ForumShortName
     FROM
         TopicsComplete T
     LEFT JOIN 
         Messages M ON M.TopicId = T.TopicId 
                    AND M.MessageId = T.LastMessageId AND M.Active = 1    
     LEFT JOIN 
         Users MU ON MU.UserId = M.UserId
     LEFT JOIN 
         Forums F ON F.ForumId = T.ForumId
     WHERE
         ISNULL(T.ReadAccessGroupId,-1) <= ISNULL(@UserGroupId,-1)
     ORDER BY 
         T.TopicId DESC

Upvotes: 0

Views: 47

Answers (1)

GMB
GMB

Reputation: 222582

If I followed you correctly, you want the 20 topics that have the most messages per day (with total of 0200 rows).

Your query has many columns so here is a simplified version of it; this works by joining the topics with an aggregated query that counts the number of related messages, then ranking and filtering.

select top(2000) *
from (
    select 
        t.*, 
        m.cnt,
        row_number() over(partition by cast(topiccreationdate as date) order by m.cnt desc) rn
    from topicscomplete t
    inner join (select topicid, count(*) cnt from messages where m.active = 1 group by topicid) m
        on m.topicid = t.topicid
    where isnull(t.readaccessgroupid,-1) <= isnull(@usergroupid,-1)
) t
where rn <= 20
order by cast(topiccreationdate as date) desc, rn

Upvotes: 0

Related Questions