<p>Here is a simple scenario with table characters:</p>

<pre><code>CharacterName GameTime Gold Live
Foo 10 100 3 
Foo 20 100 2 
Foo 30 95 2
</code></pre>

<p>How do I get this output for the query <code>SELECT Gold, Live FROM characters WHERE name = 'Foo' ORDER BY GameTime</code>:</p>

<pre><code>Gold Live
100 3
0 -1
-5 0
</code></pre>

<p>using MySQL stored procedure (or query) if it's even possible? I thought of using 2 arrays like how one would normally do in a server-side language, but MySQL doesn't have array types.</p>

<p>While I'm aware it's probably easier to do in PHP (my server-side langauge), I want to know if it's possible to do in MySQL, just as a learning material.</p>

Do you have an ID on your Table.
```
```
GameID      CharacterName GameTime    Gold        Live
----------- ------------- ----------- ----------- -----------
1           Foo           10          100         3
2           Foo           20          100         2
3           Foo           30          95          2
```
```
If so you could do a staggered join onto itself
```
```
SELECT
    c.CharacterName, 
    CASE WHEN c_offset.Gold IS NOT NULL THEN c.Gold - c_offset.Gold ELSE c.Gold END AS Gold,
    CASE WHEN c_offset.Live IS NOT NULL THEN c.Live - c_offset.Live ELSE c.Live END AS Live
FROM Characters c 
    LEFT OUTER JOIN Characters c_offset
        ON c.GameID - 1 = c_offSet.GameID
ORDER BY
    c.GameTime
```
```
Essentially it joins each game row to the previous game row and does a diff between the values. That returns the following for me.
```
```
CharacterName Gold        Live
------------- ----------- -----------
Foo           100         3
Foo           0           -1
Foo           -5          0
```
```