Results 1 to 2 of 2

Thread: Trying to learn T-SQL subquery syntax

  1. #1
    Join Date
    Dec 2012
    Location
    near San Diego, CA
    Posts
    1

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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
  •