(My)SQL- difference between two rows?

[SiD the Turtle]

I have a table that stores scores for each person each day eg:

id date score
1 date1 1000
2 date1 400
3 date1 700
1 date2 900
2 date2 400
3 date2 200
1 date3 800
2 date3 200
3 date3 100 etc.

What I want to find out is what the difference between each score for each person each day, so I end up with this:

id datea dateb diff
1 date1 date2 100
1 date2 date3 100
2 date1 date2 100
2 date2 date3 200
3 date1 date2 500
3 date2 date3 100

Any ideas on how to accomplish this? The limitation I have is that my host is using an older version of MySQl so I can't use limits in sub selects, can't create functions or views etc so it all has to be in one query. I was thinking of some kind of self join, but that brings back too many irrelevant rows (e.g. date 1 - date 3).

 

[SiD the Turtle]

I think you're on to something there. Problem I have is that the dates are in fact date times, so even if I cast them into just dates I may run into join issues as some of the dates may end up the same. I do however have a table of the possible date time values that I could try to join to somehow.

 

[SiD the Turtle]

Almost.

At the moment the data is gathered once a day when I run a program. In future I want it to run much more frequently, say once every ten minutes. So what I really need is a method to work out the difference between each two adjacent runs of the program.

I'm sure they key is in having a separate date table to record the date/time of each run and I can think of how to do it if lets say it was an array in c#, I'm just struggling in SQL!