Avatar of biotec

asked on 

Simple Row_Number over partition SQL query problem

I've got this query below but I need it to return the second row of the patient_diagnosis table only for the pd.enc_id I've filtered on. Problem is somehow I've got the sequence messed up and it's returning the pd.description for the first create_timestamp diagnosis ever. If there is only one diagnosis or if null then I need it to just return a blank but in this case I want to know it's null or blank so I put wording in there for testing.

SELECT * from
      (select ROW_NUMBER() OVER (PARTITION BY pd.enc_id ORDER BY pd.create_timestamp desc)rowno,
WHEN pd.diagnosis_code_id is not null and (pd.description is not null or pd.description <> '')
      then pd.description
ELSE 'only one description'
END )diagdescription
From patient_diagnosis pd
      where pd.enc_id = '3C9C75D6-843A-4E1E-8052-') diagdescription
      where rowno = 2
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon