# Miscellaneous > Structured Query Language (SQL) >  Trying to learn T-SQL subquery syntax

## Chri3s

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

----------


## skhanal

You can't use MAX on one column and in SELECT and no aggregate function in remaining columns. You GROUP BY PATIENT_ID and it will be allowed.

You have to use INLINE view to accomplish this

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 as P1
JOIN
(SELECT PATIENT_ID, MAX(PERIOD_NO) AS MaxCertPeriod
FROM dbo.PTC_CERT_PERIOD 
GROUP BY PATIENT_ID) as P2
ON P1.PATIENT_ID = P2.PATIENT_ID
AND P1.PERIOD_NO = P2.MaxCertPeriod

----------

