How do I reference an attribute inside a table to a value inside a new row within the same table

Question

I am trying to create a table which holds the historical running times (in minutes) for a host of athletes. The table holds a foreign key to the persons name, along with storing their new running time and previous running time, along with the date the run was performed.

I am trying to keep all records of runners in the same table. I want to refer to the old running time in the new entry of when a new running time is complete. I am struggling on how this relationship will work.

Below is a table explaining what I am trying to achieve.

---

|Name_ID {FK}|Completion_Date|New_Time|Old_Time|

| 001 | 16/02/2019 | 123 | 108 |

| 001 | 16/03/2019 | 136 | 123 |

As you the table shows, the new average from the 16/02/2019 appears as the old average in 16/03/2019.

My question is how would I construct this relationship? Is it possible to make this a relationship?

OR

Is there a more efficient way? I.e Have the following table:

---

|Name_ID {FK}|Completion_Date|New_Time|

| 001 | 16/02/2019 | 123 |

| 001 | 16/03/2019 | 136 |

and create a query that could use the Name_ID and completion_Date attributes to produce an output that made:

---

|Name_ID {FK}|Completion_Date|New_Time|Old_Time|

| 001 | 16/02/2019 | 123 | 108 |

Any help will be appreciated.

Answer 1

You can use lead/lag analytical functions to get the desired result -

Using your Name| completion_date| new_time runner_data table,

create a new table with below sql:

insert into new_table select name, completion_date, new_time, lag(completion_date) over ( partition by name order by completion_date desc) as old_time from runner_data;

Answer 2

If you don't have a Mysql 8.x Server you can use this.

CREATE TABLE table1
(`Name_ID {FK}` int, `Completion_Date` varchar(10), `New_Time` int)
;

INSERT INTO table1
(`Name_ID {FK}`, `Completion_Date`, `New_Time`)
VALUES
(001, '16/01/2019', 108),
(001, '16/02/2019', 123),
(001, '16/03/2019', 136)
;

And you can use this

select  `Name_ID {FK}`,`Completion_Date`,@quot old_time, @quot:=`New_Time` new_time
 from table1 p,(SELECT @quot := 0) r
 order by  `Name_ID {FK}`,`Completion_Date`;

to get This result:

Name_ID {FK}    Completion_Date     old_time    new_time
  1              16/01/2019            0           108
  1              16/02/2019            108         123
  1              16/03/2019            123         136

It is based on this Simulate lag function in MySQL