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 Dec 18, 2014 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? Dec 18, 2014 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. Dec 19, 2014 at 11:13

1 Answer 1

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 and acknowledge you have read our privacy policy.

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