How to get the value using another column and get difference between each row in MySQL or SQL?

finish_position    official_rating      date                Difference
    11                   NULL        2013-09-18                 0.00
     4                   NULL        2013-11-08                 0.00
     3                   NULL        2014-02-27                 0.00
     6                    65         2014-04-24                 0.00
    13                    63         2014-05-05                 0.00
     1                    59         2014-07-04                 0.00
     2                    65         2014-08-04       (59-65)= -6.00
    12                    68         2014-10-28       (59-68)= -9.00
     9                    62         2014-12-09       (59-62)= -3.00
     5                    65         2015-01-08       (59-65)= -6.00
     7                    60         2015-01-29       (59-60)= -1.00
    11                    65         2015-04-08       (59-65)= -6.00
     5                    63         2015-04-22       (59-63)= -4.00
     1                    60         2015-07-14                 0.00
     9                    60         2015-08-04       (60-60)=  0.00
     8                    56         2016-01-22       (60-56)=  4.00
     4                    52         2016-02-26       (60-52)=  8.00
     7                    50         2016-03-31       (60-50)=  10.00
     8                    48         2016-04-25       (60-48)=  12.00
     4                    56         2016-07-12       (60-56)=  4.00
     8                    47         2016-09-23       (60-47)=  13.00
     9                    52         2017-06-20       (60-52)=  8.00
     1                    50         2017-07-04                 0.00
     3                    55         2017-07-20       (50-55)=  -5.00
     3                    48         2017-07-29       (50-48)=  2.00

I have above two columns(finish_position, official_rating). I want get the above values for the difference column. Whenever finish_position is 1 then difference is 0. All above rows differences are 0 until this row (first row from top to bottom that finish_position is equal to 1) You can get the idea from the above table. I need to get the difference between each rows official_rating between the finish_positions are equal to 1. I hope you guys are understand the problem and please help me to do this.

I want the SQL code.

Answer

I have imagined to do it like this and I will try to explain.

Please note that my date column is named datec and table is called test

• First I have selected selected first part of the result where I will not calculate any difference (until first number 1 appeared). I did it with this query:

  select finish_position, official_rating, datec, 0.00 as difference
  from test
  where datec <= (select min(datec) 
                  from test 
                  where finish_position = 1
  

• Then I have selected the second part of the table, where I will calculate the difference and mark it as null because i will calculate the difference in the outer query. The second part is selected with this query:

  select finish_position, official_rating, datec, null as difference
  from test
  where datec > (select min(datec) 
                 from test 
                 where finish_position = 1)
  

• And finally I have made an union of this two results and wrote a query on it:

  select finish_position,official_rating, datec
         , case when difference = 0.00 then difference
                else official_rating - lag(official_rating) over(order by datec) 
           end difference_1
  from (
  select finish_position, official_rating, datec, 0.00 as difference
  from test
  where datec <= (select min(datec) 
                  from test 
                  where finish_position = 1)
  union 
  select finish_position, official_rating, datec, null as difference
  from test
  where datec > (select min(datec) 
                 from test 
                 where finish_position = 1)) t1
  order by datec;
  

Finally I have created a small demo for you (I did not entered whole data but…). Take a look…

After some more discussion with OP I have finally finished with this code:

select finish_position,official_rating, datec
       , case when finish_position = 1 then 0.00
          when t1.datec >= (select tt.datec 
                            from test tt
                            where DATEDIFF(tt.datec, t1.datec) in 
                                  (select max(datediff(ttt.datec, t1.datec))
                                   from test ttt
                                   where finish_position = 1
                                   and ttt.datec <= t1.datec)) 
              then (select t.official_rating 
                    from test t
                    where datediff(t.datec, t1.datec) in 
                          (select max(datediff(ttt.datec, t1.datec))
                           from test ttt
                           where finish_position = 1
                           and ttt.datec <= t1.datec)) - t1.official_rating
              end difference
from (
select finish_position, official_rating, datec
       , case when finish_position = 1 then null
              else 0.00 
         end as difference
from test
where datec <= (select min(datec) 
                from test 
                where finish_position = 1)
union 
select finish_position, official_rating, datec, null as difference
from test
where datec > (select min(datec) 
                from test 
                where finish_position = 1)) t1
order by datec;

Here is the second DEMO