Get Last message loaded based on message type

Question

I have a table which contains different messages, each with a messageType field. I need to fill a ASP.NET list control of the last loaded message for each messageType. My sql statement to return everything is:

SELECT MessageTypes.MessageType MessageType
    ,Messages.MessageDate
    ,Messages.ValueDate
    ,Messages.MessageReference
    ,Messages.Beneficiary
    ,Messages.StatusId
    ,MessageStatus.STATUS
    ,BICProfile.BIC
FROM Messages
INNER JOIN MessageStatus
    ON Messages.StatusId = MessageStatus.Id
INNER JOIN MessageTypes
    ON Messages.MessageTypeId = MessageTypes.MessageTypeId
INNER JOIN BICProfile
    ON Messages.SenderId = dbo.BICProfile.BicId
WHERE (BICProfile.BIC = 'someValue')
    AND Messages.StatusId IN (4, 5, 6)

So i need to pull back the last message for each message type.

EDIT Some clarification on what the statusId is. These are basically used in conjunction with a users role, what messages that can see. So, i need to return the last loaded message for a given message type. Effectively, only looking to return 1 message for each message type. The messageId is unique, where the message date could be the same.

Answer 1

You can use the ROW_NUMBER() Function to assign each of your messages a rank by Message date (starting at 1 again for each message type), then just limit the results to the top ranked message:

WITH AllMessages AS
(   SELECT  MessageTypes.MessageType, 
            Messages.MessageDate, 
            Messages.ValueDate, 
            Messages.MessageReference, 
            Messages.Beneficiary, 
            Messages.StatusId,
            MessageStatus.Status, 
            BICProfile.BIC,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY Messages.MessageTypeId 
                                            ORDER BY Messages.MessageDate DESC)
    FROM    Messages 
            INNER JOIN MessageStatus 
                ON Messages.StatusId = MessageStatus.Id 
            INNER JOIN MessageTypes 
                ON Messages.MessageTypeId = MessageTypes.MessageTypeId 
            INNER JOIN BICProfile  
                ON Messages.SenderId = dbo.BICProfile.BicId 
    WHERE   BICProfile.BIC = 'someValue'
    AND     Messages.StatusId IN (4, 5, 6)
)
SELECT  MessageType, 
        MessageDate, 
        ValueDate, 
        MessageReference, 
        Beneficiary, 
        StatusId,
        Status, 
        BIC 
FROM    AllMessages
WHERE   RowNumber = 1;

If you can't use ROW_NUMBER then you can use a subquery to get the latest message date per type:

SELECT  Messages.MessageTypeID, MessageDate = MAX(Messages.MessageDate)
FROM    Messages
        INNER JOIN BICProfile  
            ON Messages.SenderId = dbo.BICProfile.BicId 
WHERE   BICProfile.BIC = 'someValue'
AND     Messages.StatusId IN (4, 5, 6)
GROUP BY Messages.MessageTypeID

Then inner join the results of this back to your main query to filter the results:

SELECT  MessageTypes.MessageType, 
        Messages.MessageDate, 
        Messages.ValueDate, 
        Messages.MessageReference, 
        Messages.Beneficiary, 
        Messages.StatusId,
        MessageStatus.Status, 
        BICProfile.BIC
FROM    Messages 
        INNER JOIN MessageStatus 
            ON Messages.StatusId = MessageStatus.Id 
        INNER JOIN MessageTypes 
            ON Messages.MessageTypeId = MessageTypes.MessageTypeId 
        INNER JOIN BICProfile  
            ON Messages.SenderId = dbo.BICProfile.BicId 
        INNER JOIN 
        (   SELECT  Messages.MessageTypeID, 
                    MessageDate = MAX(Messages.MessageDate)
            FROM    Messages
                    INNER JOIN BICProfile  
                        ON Messages.SenderId = dbo.BICProfile.BicId 
            WHERE   BICProfile.BIC = 'someValue'
            AND     Messages.StatusId IN (4, 5, 6)
            GROUP BY Messages.MessageTypeID
        ) AS MaxMessage
            ON MaxMessage.MessageTypeID = Messages.MessageTypeID
            AND MaxMessage.MessageDate = Messages.MessageDate
WHERE   BICProfile.BIC = 'someValue'
AND     Messages.StatusId IN (4, 5, 6);

N.B This second method will return multiple rows per message type if the latest message date is common among more than one message. This behaviour can be replicated in the first query by replacing ROW_NUMBER with RANK

---

EDIT

If you will have multiple messages with the same date and only want to return one of them you need to expand the ordering within the row_number function, i.e. if you wanted to pick the message with the maximum id when there were ties you could make it:

RowNumber = ROW_NUMBER() OVER(PARTITION BY Messages.MessageTypeId 
                                ORDER BY Messages.MessageDate DESC,
                                        Messages.MessageID DESC)

So the full query would be:

WITH AllMessages AS
(   SELECT  MessageTypes.MessageType, 
            Messages.MessageDate, 
            Messages.ValueDate, 
            Messages.MessageReference, 
            Messages.Beneficiary, 
            Messages.StatusId,
            MessageStatus.Status, 
            BICProfile.BIC,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY Messages.MessageTypeId 
                                            ORDER BY Messages.MessageDate DESC,
                                                    Messages.MessageID DESC)
    FROM    Messages 
            INNER JOIN MessageStatus 
                ON Messages.StatusId = MessageStatus.Id 
            INNER JOIN MessageTypes 
                ON Messages.MessageTypeId = MessageTypes.MessageTypeId 
            INNER JOIN BICProfile  
                ON Messages.SenderId = dbo.BICProfile.BicId 
    WHERE   BICProfile.BIC = 'someValue'
    AND     Messages.StatusId IN (4, 5, 6)
)
SELECT  MessageType, 
        MessageDate, 
        ValueDate, 
        MessageReference, 
        Beneficiary, 
        StatusId,
        Status, 
        BIC 
FROM    AllMessages
WHERE   RowNumber = 1;

Answer 2

You can use a simple subquery to get the laste messagedate for each message type and use that to filter in on the last message. See below:

If you want the last message for each category, only where the status of that message is 4, 5, or 6: (no result for that category if the status of the message is something else)

SELECT MessageTypes.MessageType MessageType,
       Messages.MessageDate,
       Messages.ValueDate,
       Messages.MessageReference,
       Messages.Beneficiary,
       Messages.StatusId,
       MessageStatus.STATUS,
       BICProfile.BIC
  FROM Messages
 INNER JOIN MessageStatus
    ON Messages.StatusId = MessageStatus.Id
 INNER JOIN MessageTypes
    ON Messages.MessageTypeId = MessageTypes.MessageTypeId
 INNER JOIN BICProfile
    ON Messages.SenderId = dbo.BICProfile.BicId
 WHERE BICProfile.BIC = 'someValue'
   AND Messages.StatusId IN (4, 5, 6)
   and messages.messagedate =
       (select max(x.messagedate)
          from messages x
         where x.messagetypeid = messages.messagetypeid)

If you want the last message for each category, only where the status of that message is 4, 5, or 6: (no result for that category if the status of the message is something else) AND IN THE EVENT OF A TIE YOU WANT THE MESSAGE WITH THE HIGHEST MESSAGE ID

SELECT MessageTypes.MessageType MessageType,
       Messages.MessageDate,
       Messages.ValueDate,
       Messages.MessageReference,
       Messages.Beneficiary,
       Messages.StatusId,
       MessageStatus.STATUS,
       BICProfile.BIC
  FROM Messages
 INNER JOIN MessageStatus
    ON Messages.StatusId = MessageStatus.Id
 INNER JOIN MessageTypes
    ON Messages.MessageTypeId = MessageTypes.MessageTypeId
 INNER JOIN BICProfile
    ON Messages.SenderId = dbo.BICProfile.BicId
 WHERE BICProfile.BIC = 'someValue'
   AND Messages.StatusId IN (4, 5, 6)
   and messages.messagedate =
       (select max(x.messagedate)
          from messages x
         where x.messagetypeid = messages.messagetypeid)
   and messages.messageid =
       (select max(x.messageid)
          from messages x
         where x.messagetypeid = messages.messagetypeid
           and x.messagedate = messages.messagedate)

^^ CHANGE "messageid" to whatever field on table MESSAGES indicates the ID.

If you want the last message for each category and for each status of 4, 5, and 6 (if any/all exist within that category)

SELECT MessageTypes.MessageType MessageType,
       Messages.MessageDate,
       Messages.ValueDate,
       Messages.MessageReference,
       Messages.Beneficiary,
       Messages.StatusId,
       MessageStatus.STATUS,
       BICProfile.BIC
  FROM Messages
 INNER JOIN MessageStatus
    ON Messages.StatusId = MessageStatus.Id
 INNER JOIN MessageTypes
    ON Messages.MessageTypeId = MessageTypes.MessageTypeId
 INNER JOIN BICProfile
    ON Messages.SenderId = dbo.BICProfile.BicId
 WHERE BICProfile.BIC = 'someValue'
   AND Messages.StatusId IN (4, 5, 6)
   and messages.messagedate =
       (select max(x.messagedate)
          from messages x
         where x.messagetypeid = messages.messagetypeid
           and x.statusid = messages.statusid)

If you want the last message for each category where the status is 4, 5, or 6: (messages of other statuses not considered)

SELECT MessageTypes.MessageType MessageType,
       Messages.MessageDate,
       Messages.ValueDate,
       Messages.MessageReference,
       Messages.Beneficiary,
       Messages.StatusId,
       MessageStatus.STATUS,
       BICProfile.BIC
  FROM Messages
 INNER JOIN MessageStatus
    ON Messages.StatusId = MessageStatus.Id
 INNER JOIN MessageTypes
    ON Messages.MessageTypeId = MessageTypes.MessageTypeId
 INNER JOIN BICProfile
    ON Messages.SenderId = dbo.BICProfile.BicId
 WHERE BICProfile.BIC = 'someValue'
   AND Messages.StatusId IN (4, 5, 6)
   and messages.messagedate =
       (select max(x.messagedate)
          from messages x
         where x.messagetypeid = messages.messagetypeid
           and x.statusid in (4, 5, 6))

Answer 3

Try this:

SELECT MT.MessageType
    ,X.MessageDate
    ,X.ValueDate
    ,X.MessageReference
    ,X.Beneficiary
    ,X.StatusId
    ,X.STATUS
    ,X.BIC
FROM MessageTypes MT
OUTER APPLY -- or CROSS APPLY
(
SELECT TOP 1
    M.MessageDate
    ,M.ValueDate
    ,M.MessageReference
    ,M.Beneficiary
    ,M.StatusId
    ,MS.STATUS
    ,B.BIC
FROM Messages M
INNER JOIN MessageStatus MS
    ON M.StatusId = MS.Id
INNER JOIN BICProfile B
    ON M.SenderId = B.BicId
WHERE (B.BIC = 'someValue')
    AND M.StatusId IN (4, 5, 6)
    AND M.MessageTypeId = MT.MessageTypeId
ORDER BY M.MessageDate DESC
) X