locked
How to find last message and unread message count per sender RRS feed

  • Question

  • User1501362304 posted

    Hi,

    I have this basic table UserChat, which is defind as below.

    CREATE TABLE [dbo].[UserChat]
    (
    	[Id] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    	[SentBy] INT NOT NULL,
    	[SentTo] INT NOT NULL,
    	[Message] VARCHAR(1000) NOT NULL,
    	[SentOn] DATETIME2(3) NOT NULL,
    	[ReadOn] DATETIME2(3) NULL
    )
    GO

    And has below sample data.

    Id	SentBy	SentTo	Message	SentOn	                ReadOn
    1	1	2	msg 1	2020-07-31 17:22:45.017	NULL
    2	1	2	msg 2	2020-07-31 17:22:56.933	NULL
    3	1	3	msg 3	2020-07-31 17:23:12.793	NULL
    4	2	3	msg 4	2020-07-31 17:23:26.253	NULL
    5	2	1	msg 5	2020-07-31 17:23:36.367	NULL
    6	4	1	msg 6	2020-07-31 17:23:48.070	NULL
    7	3	1	msg 7	2020-07-31 17:31:34.837	NULL
    8	4	3	msg 8	2020-07-31 17:32:01.027	NULL
    9	4	4	msg 9	2020-07-31 17:32:30.800	NULL
    10	4	1	msg 10	2020-07-31 17:32:56.387	NULL
    11	1	2	msg 11	2020-07-31 17:38:50.340	NULL
    12	1	3	msg 12	2020-07-31 13:02:16.427	NULL
    13	2	4	msg 13	2020-07-31 18:08:28.970	NULL
    14	4	3	msg 14	2020-07-31 18:08:38.110	NULL
    15	1	3	msg 15	2020-07-31 18:08:56.460	NULL
    16	2	1	msg 16	2020-07-31 18:47:55.220	NULL
    17	1	2	msg 17	2020-07-31 18:51:43.493	NULL
    18	2	1	msg 18	2020-07-31 18:53:42.380	NULL

    I want to fetch record for a user say user id = 1 (It could be in SentBy or SentTo above) such that it returns last message per user and total unread messages per row (unread message would be where SentTo is given userId and ReadOn is null). Think it like whatsapp user chat list. 

    I am able to find last message id per user but stuck in unread count and actual message text. Below is my query. Please tell me how to modify that to return the desired result. 

    DECLARE @userId INT = 1
    
    SELECT MAX(Id) AS LatestMessageId
    --,COUNT(CASE WHEN SentTo = @userId AND ReadOn IS NULL THEN 1 ELSE 0 END) AS UnreadMessages
    FROM tmpChat
    WHERE SentBy = @userId OR SentTo = @userId
    GROUP BY (CASE WHEN SentBy = @userId THEN SentTo WHEN SentTo = @userId THEN SentBy END)
    

    Thanks

    Friday, July 31, 2020 4:52 PM

Answers

  • User452040443 posted

    Hi,

    Try in your original query:

    ,COUNT(CASE WHEN SentTo = @userId AND ReadOn IS NULL THEN 1 END) AS UnreadMessages

    But I believe it would be more accurate to use the SentOn column to get the last message.

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 2, 2020 6:38 PM

All replies

  • User452040443 posted

    Hi,

    Try:

    select top(1) 
        c.*,
        (select count(1) from UserChat as s where s.SentTo = @userId) as UnreadMessages
    from UserChat as c
    where c.SentBy = @userId OR c.SentTo = @userId
    order by c.SentOn desc
    

    Hope this help

    Friday, July 31, 2020 5:26 PM
  • User1501362304 posted

    Hi imapsp, above query does not seem working. 

    1. I want unread message count for every user with whom given user is chatting (as in whatsapp e.g.)
    2. If I remove top(1) then it returns wrong data which is not relevant at all. 
    3. Is there a way to avoid sub query/inner query?

    Thanks

    Saturday, August 1, 2020 3:45 PM
  • User452040443 posted

    Hi,

    Can you post the expected result considering the sample data you posted?

    Saturday, August 1, 2020 5:08 PM
  • User1501362304 posted

    Hi,

    Expected result is 

    LastMessageId  UnreadMessages
    18             3
    15             1
    10             2  

    Thanks

    Sunday, August 2, 2020 2:40 PM
  • User452040443 posted

    Hi,

    Try in your original query:

    ,COUNT(CASE WHEN SentTo = @userId AND ReadOn IS NULL THEN 1 END) AS UnreadMessages

    But I believe it would be more accurate to use the SentOn column to get the last message.

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 2, 2020 6:38 PM
  • User1501362304 posted

    oops, I had glitch in my original query, it was counting 0 as well in else part or I should have used SUM instead of COUNT. 

    Thanks for pointing out the issue.

    Monday, August 3, 2020 11:53 AM