-
Trying to learn T-SQL subquery syntax
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|