How to select separate rows from a table without a primary key

advertisements

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??