1

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

3
  • you can refer to this website it may help you ozeki.hu/index.phtml?ow_page_number=171 to track SMS status – Ahmad Abuhasna Dec 18 '14 at 10:17
  • Do you want to be able to provide a history of all statuses that the message has been through, or just the latest status? – Phil Sumner Dec 18 '14 at 10:53
  • Just the latest statuses of all users for single message. means An message to multiple users. then after success, i will flush this out and waiting for new data to come and again do the previous action. – Muhammad Taqi Dec 19 '14 at 11:13
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.