I am trying to create MySQL dataset which has entries from previous study (surgical study with the first study having value of 1) as well for that userid. Each user can have multiple studies. I am trying to use Lag function to get previous study data. I am getting duplicate values for study 1 while trying to use the lag function.
WITH Complete_Data AS ( SELECT * FROM Data GROUP BY UserId, studyno ) SELECT *, LAG(indication_of_failure, 1) OVER ( PARTITION BY UserId ORDER BY studyno ) prev_study_indication_of_failure, LAG(BCVA_State,1) OVER ( PARTITION BY UserId ORDER BY studyno ) prev_study_BCVA FROM Complete_Data GROUP BY UserId, studyno;
I am expecting nulls in prev_study_indication_of_failure and prev_study_BCVA when studyno = 1 but filled with values when studyno >=2.
Due to duplicate entries for studyno = 1, I am getting values in prev_study_indication_of_failure and prev_study_BCVA