How can I select the entire row when using max

Question

I have a table with the following columns:

SignatureID
PatientID
PatientVisitID
TreatAuthDate
HIPAADate
DrugTestDate

Now I have the following Select statement:

SELECT *
FROM tblSignature
WHERE PatientID = 12345

This select statement returns 8 rows. What I need to accomplish is getting the MAX TreatAuthDate - and with that MAX TreatAuthDate I need the PatientVisitID. Then I need the same type of information for the HipaaDate and DrugTestDate. How can I do this?

Answer 1

SELECT  TOP 1 *
FROM    tblSignature 
WHERE   PatientID = 12345
ORDER BY
        TreatAuthDate DESC

To get three last results for different definitions of "last", use this:

SELECT  *
FROM    (
        SELECT  TOP 1 'LastThreatAuth' AS which, ts.*
        FROM    tblSignature ts
        WHERE   PatientID = 12345
        ORDER BY
                TreatAuthDate DESC
        ) SrcTreatAuth 
UNION ALL
SELECT  *
FROM    (
        SELECT  TOP 1 'LastHIPAA' AS which, ts.*
        FROM    tblSignature ts
        WHERE   PatientID = 12345
        ORDER BY
                HIPAADate DESC
        ) SrcHIPAA
UNION ALL
SELECT  *
FROM    (
        SELECT  TOP 1 'LastDrugTest' AS which, ts.*
        FROM    tblSignature ts
        WHERE   PatientID = 12345
        ORDER BY
                DrugTestDate DESC
        ) SrcDrugTest

Answer 2

SELECT patientid, max(Treatauthdate), max (HippaDAte) , max (DrugTestDate) FROM tblSignature WHERE PatientID = 12345 group by patientid

Note you can't ask for signatureid in this case as you would not filter any records out (I'm making the assumption signatureid is your PK). Further to get the max of each date per patient, it is likely they are each on a differnt row of the table so would not have the same signatureid.

To get the visit date for each type might be more difficult as each may be a separate visit. try something like

select a.patientid, Treatvisitdate, Treatauthdate,Hippavisitdate, HippaDate, DrugTestvisitdate, 
DrugTestDate
(SELECT  patientid, patientvisitdate as Treatvisitdate, max(Treatauthdate) as Treatauthdate
FROM tblSignature
WHERE PatientID = 12345
group by patientid,patientvisitdate)a
join
(SELECT  patientid, patientvisitdate as Hippavisitdate, max(HippaDate) as HippaDate
FROM tblSignature
WHERE PatientID = 12345
group by patientid,patientvisitdate) b on a.patientid = b.patientid
join
(SELECT  patientid, patientvisitdate as DrugTestvisitdate, max(DrugTestDate) as DrugTestDate
FROM tblSignature
WHERE PatientID = 12345
group by patientid,patientvisitdate) c on a.patientid = c.patientid

YOu might need left joins if some of the dates might not be in there.