SQL - Group By unique column combination

[PhilEaton]

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?

[PhilEaton]

Here is what I ended up using:

select 
  ts.createtimestamp, 
  ts.isComplete, 
  tsi.status, 
  tsi.physicianid, 
  tsi.documentIdDisplay, Utility.SqlFunctions_NdnListToAccountList(tsi.NdocNum) AS AccountNum
From 
  Interface_SFAX.TransmissionSend ts INNER JOIN  Interface_SFAX.TransmissionSendItem tsi ON
      ts.ID = tsi.childsub
  where
    tsi.physicianid='53698425' and tsi.documentiddisplay='485-32' and Utility.SqlFunctions_NdnListToAccountList(tsi.NdocNum) = '1000359'
  group by
    tsi.physicianid,
    tsi.documentIdDisplay, Utility.SqlFunctions_NdnListToAccountList(tsi.NdocNum)
  having ts.createtimestamp = max(ts.createtimestamp)