Need Help in building database

database-designrdbmsschema

I am developing an app that send messages (email and sms) to users. The db must also maintains the list of messages send to users. And also the status of messages send to user like send or failed.

I have build this scheme that 1 message have many users and 1 reciept is generated to show the status against each message.

Here the confusion is that how would i maintain the status of a message for every user that message is particular user is send or failed.

enter image description here

Best Answer

To find the latest status of all message (assuming receiptID is an auto-incrementing key) you should use a window function:

SELECT Status
     , messageID
FROM (SELECT Status
           , messageID
           , Row_Number() over (partition by messageID order by receiptID desc) priority
      FROM Receipt) PrioritizedStatus
WHERE PrioritizedStatus.priority = 1
Related Question