Hi,

I'm new here so hopefully have this in the right forum.

I have a table PTC_CERT_PERIOD and want to get the Max PERIOD_NO for each PATIENT_ID in the table. I can do this by having my view look at another view like below:

SELECT TOP (100) PERCENT dbo.PTC_CERT_PERIOD.CERT_PERIOD_ID, dbo.PTC_CERT_PERIOD.PATIENT_ID, dbo.PTC_CERT_PERIOD.CERTIFICATION_DATE,
dbo.PTC_CERT_PERIOD.CERT_END_DATE, dbo.PTC_CERT_PERIOD.PAY_SOURCE_POINTER, dbo.PTC_CERT_PERIOD.IS_ACTIVE,
dbo.VW_SDH_CAS_MaxCertPeriod1.MaxCertPeriod, dbo.PTC_CERT_PERIOD.CREATE_DATE
FROM dbo.PTC_CERT_PERIOD INNER JOIN

dbo.VW_SDH_CAS_MaxCertPeriod1 ON dbo.PTC_CERT_PERIOD.PATIENT_ID = dbo.VW_SDH_CAS_MaxCertPeriod1.PATIENT_ID AND
dbo.PTC_CERT_PERIOD.PERIOD_NO = dbo.VW_SDH_CAS_MaxCertPeriod1.MaxCertPeriod


But it seems to me that I should be able to write a subquery to do the same thing and I can't get the syntax right for the subquery. I've been trying for hours, my latest failed iteration is:

SELECT CERT_PERIOD_ID, PATIENT_ID, CERTIFICATION_DATE, CERT_END_DATE, PAY_SOURCE_POINTER, IS_ACTIVE, CREATE_DATE, PERIOD_NO
FROM dbo.PTC_CERT_PERIOD
WHERE (PERIOD_NO IN
(SELECT PATIENT_ID AS MaxPtID, MAX(PERIOD_NO) AS MaxCertPeriod
FROM dbo.PTC_CERT_PERIOD AS PTC_CERT_PERIOD_1))


It seems like anything I try gets an error message like "Column dbo.PTC-CERT_PERIOD.PATIENT_ID is invalid in the select list because it is not contained in eithe an aggregate function or in the GROUP BY clause. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. I've played around with several EXISTS statemsnts and if they run the results are way off, listing all the PERIOD_NO for the patient, not just the Max. Can anyone point me on the right track?

Thanks,

Chris