I need to retrive last of these results? (T-SQL)

Question

This query:

SELECT refPatient_id,actDate,refReason_id,refClinic_id,active
FROM PatientClinicHistory
WHERE refClinic_id = 24 
GROUP BY refPatient_id,actDate,refReason_id,refClinic_id,active
ORDER BY refPatient_id,actDate

returns this result:

refPatient_id     actDate                refReason_id   refClinic_id  active
=============     ====================   ============   ============  ======
15704             2009-02-09 12:48:00    19             24            0
15704             2009-02-10 10:25:00    23             24            1
15704             2009-02-10 10:26:00    19             24            0
15704             2009-02-12 10:16:00    23             24            1
15704             2009-02-13 15:41:00    19             24            0
15704             2009-04-14 17:48:00    19             24            0
15704             2009-06-24 16:06:00    19             24            0
15731             2009-05-20 12:19:00    19             24            0
16108             2009-07-20 11:08:00    19             24            0
16139             2009-03-02 13:55:00    19             24            0
16569             2009-07-13 15:57:00    20             24            0
17022             2009-06-02 16:02:00    19             24            0
17022             2009-08-19 15:08:00    19             24            0
17022             2009-09-01 15:47:00    21             24            0
17049             2009-02-02 16:49:00    19             24            0
17049             2009-02-04 15:16:00    19             24            0
17063             2009-07-22 11:35:00    21             24            0
17063             2009-07-28 10:14:00    22             24            1
17502             2008-12-15 17:25:00    19             24            0

I need to get every patient's last passive action row (active = 0) (so i need to obtain the maximum actDate for each patient)

Should i write a new query after i get all these results in order to filter it?

Edited: Thank you for your responses, actually i need to get last action for each patient. forex:

17022             2009-06-02 16:02:00    19             24            0
17022             2009-08-19 15:08:00    19             24            0
17022             2009-09-01 15:47:00    21             24            0

I need to filter the last row(max actDate for each patient).

17022             2009-09-01 15:47:00    21             24            0

Answer 1

SELECT refPatient_id,MAX(actDate)
FROM PatientClinicHistory
WHERE refClinic_id = 24 
GROUP BY refPatient_id

will calculate maximum actDate for each patient. Is it what you want?

Answer 2

You could try taking the actDate out of the group by and using max function max(actDate)

like

 SELECT refPatient_id,Max(actDate),refReason_id,refClinic_id,active
          FROM PatientClinicHistory
         WHERE refClinic_id = 24 
           AND active = 0
  GROUP BY refPatient_id,refReason_id,refClinic_id,active
  ORDER BY refPatient_id

Answer 3

You could use a CTE

;WITH PatientClinicHistoryNew AS
(
    SELECT refPatient_id,actDate,refReason_id,refClinic_id,active
    FROM PatientClinicHistory
    WHERE refClinic_id = 24 
    GROUP BY refPatient_id,actDate,refReason_id,refClinic_id,active
    ORDER BY refPatient_id,actDate
)
SELECT refPatient_id, Max (actDate)
FROM PatientClinicHistoryNew
WHERE 1=1
    AND active = 0
GROUP BY refPatient_id