0

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


Sample Data enter image description here


Expected Outcome enter image description here

  • Maybe the output of "SHOW CREATE TABLE (Complete_)Data\G" would be good here. Even some (obviously obsfuscated!) data would be a help? p.s. welcome to the forum! :-) – Vérace Apr 17 at 6:44
  • I am expecting nulls in prev_study_indication_of_failure and prev_study_BCVA when studyno = 1 but filled with values when studyno >=2. Add Data's DDL and some sample data into the question text (or create fiddle) to reproduce. – Akina Apr 17 at 6:45
  • Thanks for responding so quickly to my request for data, but please read this. Could you put the material you posted in the form of INSERT INTO TABLE blah VALUES (...) and also please provide the CREATE TABLE blah (...); i.e. the output of SHOW CREATE TABLE blah\G. – Vérace Apr 17 at 7:17
  • Expected Outcome The shown values for prev_study_indication_of_failure is NOT lag(). Lag is physically previous value. Select rows with ROW_NUMBER()=1 in CTE and join them to your table to obtain the data you need. – Akina Apr 17 at 8:42
  • Shouldn't ORDER BY studyno have another column to make it deterministic? – Rick James Apr 25 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.