0 down vote favorite
I am trying to write a script that will return the latest values for a unique documentid-physician-patient triplet. I need the script to act similar to a group by statement, except group by only works with one column at a time. I need to date and status information for only the most recent unique triplet. Please let me know what you will need to see from me to help. Here is the current, very bare, statement:
SELECT TransmissionSend.CreateTimestamp, TransmissionSendItem.Status, TransmissionSendItem.PhysicianId, TransmissionSendItem.DocumentIdDisplay, Utility.SqlFunctions_NdnListToAccountList(TransmissionSendItem.NdocNum) AS AccountNum FROM Interface_SFAX.TransmissionSend, Interface_SFAX.TransmissionSendItem WHERE TransmissionSend.ID = TransmissionSendItem.childsub --I don't know exactly what this does, I did not write this script. It must stay here though for the exact results. ORDER BY TransmissionSend.CreateTimestamp DESC -- In the end, each latest result of the unique triplet will be ordered from most recent to oldest in return
My question is, again, how can I limit results to only the latest status for each physician id, document id, and account number combination?