How can I select the whole 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. I need to execute MAX TreatAuthDate - and with this MAX TreatAuthDate I need PatientVisitID. Then I need the same type of information for HipaaDate and DrugTestDate. How can i do this?

Answer 1

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

To get the last three 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

, , ( , - ). , , , , .

, . -

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

, , .