The scenario is we have assignments and feedback given by the student after the assignment. They are not related in the database (except by course and student). There are multiples of each. A student can be retested in an assignment and can give feedback multiple times.
Take the following scenario:
ASSIGNMENT
Name time (these are really Unixtimestamps)
A1 0600
A1 1800
A2 0700
FEEDBACK
Assignment time key
A1 0700 feedback 1
A1 1200 feedback 2
A1 1900 feedback 3
The student sat assignment A1 at 0600 and provided their feedback at 0700 (feedback 1). They changed their mind and updated their feedback at 1200 (feedback 2). They were then retested in assignment A1 at 1800 and provided their feedback to the restest at 1900 (feedback 3). Separately they sat A2 and didn’t provide feedback.
We need to match the feedback to the assignment based on time.
The output should be (one row per row from the assignment table)
Assignment assignmenttime feedbacktime Feedback
A1 0600 0700 feedback 1
A1 1800 1900 feedback 3
A2 0700 NULL NULL
Feedback1 relates to A1 because I can join on the name and feedback.time is greater than assignment.time. My problem is if there are multiple feedback records I just want the smallest one that is larger than the assignment time. If there's no feedback record matching an assignment / time then we still want the assignment record.
What’s the MYSQL to make this happen? Thanks.
As per my understanding you need the first feedback time after the submission of assignment.
Can you try the below query -
SELECT NAME,
assginment_time,
feedback_time,
col3
FROM (SELECT t1.NAME,
t1.time1 AS assginment_time,
t2.time1 AS feedback_time,
t2.col3,
row_number()
OVER (
partition BY t1.NAME, t1.time1
ORDER BY t2.time1) AS rnk
FROM (SELECT NAME,
time1,
COALESCE(lead(time1)
OVER (
partition BY NAME
ORDER BY time1), 9999) next_time
FROM assignments) t1
LEFT OUTER JOIN feedback t2
ON t1.NAME = t2.col1
AND t2.time1 BETWEEN t1.time1 AND t1.next_time) A
WHERE A.rnk = 1;
Have Changed the column names, please refer to dbfiddle link - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1eac67b052e435e055608e95668a436f