mysql - How to get the difference of a column between the most current date and the earliest date on multiple rows -

here's columns table users.

+--------+-----------------+------+-----+---------+----------------+ | field  | type            | null | key | default |          | +--------+-----------------+------+-----+---------+----------------+ | uid    | int(6) unsigned | yes  |     | null    |                | | score  | decimal(6,2)    | yes  |     | null    |                | | status | text            | yes  |     | null    |                | | date   | datetime        | yes  |     | null    |                | | cid    | int(7) unsigned | no   | pri | null    | auto_increment | +--------+-----------------+------+-----+---------+----------------+ 

i want difference between user's current score , earliest score. tried:

select co1.uid, co1.score, co1.date users co1, (select uid, score, min(date) users group uid) co2 co2.uid = co1.uid; 

this not work. tried

select co1.uid, co1.score, co1.date users co1, (select uid, score, max(date) - min(date) users group uid) co2 co2.uid = co1.uid; 

result get:http://pastebin.com/ser81wbe

result want:

uid  max(score)-min(score) 1        40 2       -60 3        23 

etc

i think simplest solution 2 joins:

select u.uid, umin.score, umax.score (select uid, min(date) mind, max(date) maxd       users       group uid      ) u join      users umin      on u.uid = umin.uid , umin.date = u.mind join      users umax      on u.uid = umax.uid , umax.date = u.maxd; 

i should note: if know scores increasing, can simpler:

select uid, min(score), max(score) users group uid;