I need to show a Notification on user login if there is any unread messages. So if multiple users send (5 messages each) while the user is in offline these messages should be shown on login. Means have to show the last messages from each user.
I use joining to find records.
In this scenario Message from User is not a primary key.
This is my query
SELECT UserMessageConversations.MessageFrom, UserMessageConversations.MessageFromUserName, UserMessages.MessageTo, UserMessageConversations.IsGroupChat, UserMessageConversations.IsLocationChat, UserMessageConversations.Message, UserMessages.UserGroupID,UserMessages.LocationID FROM UserMessageConversations LEFT OUTER JOIN UserMessages ON UserMessageConversations.UserMessageID = UserMessages.UserMessageID WHERE UserMessageConversations.MessageTo = 743 AND UserMessageConversations.ReadFlag = 0
This is the output obtained from above query.
MessageFrom -582 appears twice. I need only one record of this user.
How is it possible
I'm not entirely sure I totally understand your question - but one approach would be to use a CTE (Common Table Expression).
With this CTE, you can partition your data by some criteria - i.e. your
MessageFrom - and have SQL Server number all your rows starting at 1 for each of those partitions, ordered by some other criteria - this is the point that's entirely unclear from your question, whether you even care what the rows for each
MessageFrom number are sorted on (do you have some kind of a
MessageDate or something that you could order by?) ...
So try something like this:
;WITH PartitionedMessages AS ( SELECT umc.MessageFrom, umc.MessageFromUserName, um.MessageTo, umc.IsGroupChat, umc.IsLocationChat, umc.Message, um.UserGroupID, um.LocationID , ROW_NUMBER() OVER(PARTITION BY umc.MessageFrom ORDER BY MessageDate DESC) AS 'RowNum' <=== totally unclear yet FROM dbo.UserMessageConversations umc LEFT OUTER JOIN dbo.UserMessages um ON umc.UserMessageID = um.UserMessageID WHERE umc.MessageTo = 743 AND umc.ReadFlag = 0 ) SELECT MessageFrom, MessageFromUserName, MessageTo, IsGroupChat, IsLocationChat, Message, UserGroupID, LocationID FROM PartitionedMessages WHERE RowNum = 1
Here, I am selecting only the "first" entry for each "partition" (i.e. for each
MessageFrom) - ordered by a "imagined"
MessageDate column so that the most recent (the newest) message would be selected.
Does that approach what you're looking for??