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

Question

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 1

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

Answer 2

Edited:

Here below is a temporary solution that yields the expected output. I edited because there were a few conditions I didn't include.

You can copy the code and paste it at here and run: https://rextester.com/l/postgresql_online_compiler

WITH LOG AS
(
    SELECT 11 AS FINISH_POSITION
         , NULL AS OFFICIAL_RATING
         , '2013-09-18' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 4 AS FINISH_POSITION
         , NULL AS OFFICIAL_RATING
         , '2013-11-08' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 3 AS FINISH_POSITION
         , NULL AS OFFICIAL_RATING
         , '2014-02-27' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 6 AS FINISH_POSITION
         , '65' AS OFFICIAL_RATING
         , '2014-04-24' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 13 AS FINISH_POSITION
         , '63' AS OFFICIAL_RATING
         , '2014-05-05' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 1 AS FINISH_POSITION
         , '59' AS OFFICIAL_RATING
         , '2014-07-03' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 2 AS FINISH_POSITION
         , '65' AS OFFICIAL_RATING
         , '2014-08-04' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 12 AS FINISH_POSITION
         , '68' AS OFFICIAL_RATING
         , '2014-10-28' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 9 AS FINISH_POSITION
         , '62' AS OFFICIAL_RATING
         , '2014-12-09' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 5 AS FINISH_POSITION
         , '65' AS OFFICIAL_RATING
         , '2015-01-08' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 7 AS FINISH_POSITION
         , '60' AS OFFICIAL_RATING
         , '2015-01-29' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 11 AS FINISH_POSITION
         , '65' AS OFFICIAL_RATING
         , '2015-04-08' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 5 AS FINISH_POSITION
         , '63' AS OFFICIAL_RATING
         , '2015-04-22' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 1 AS FINISH_POSITION
         , '60' AS OFFICIAL_RATING
         , '2015-07-14' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 9 AS FINISH_POSITION
         , '60' AS OFFICIAL_RATING
         , '2015-08-04' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 8 AS FINISH_POSITION
         , '65' AS OFFICIAL_RATING
         , '2016-01-22' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 4 AS FINISH_POSITION
         , '52' AS OFFICIAL_RATING
         , '2016-02-26' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 7 AS FINISH_POSITION
         , '50' AS OFFICIAL_RATING
         , '2016-03-31' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 8 AS FINISH_POSITION
         , '48' AS OFFICIAL_RATING
         , '2016-04-25' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 4 AS FINISH_POSITION
         , '56' AS OFFICIAL_RATING
         , '2016-07-12' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 8 AS FINISH_POSITION
         , '47' AS OFFICIAL_RATING
         , '2016-09-23 ' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 9 AS FINISH_POSITION
         , '52' AS OFFICIAL_RATING
         , '2017-06-20' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 1 AS FINISH_POSITION
         , '50' AS OFFICIAL_RATING
         , '2017-07-04' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 3 AS FINISH_POSITION
         , '55' AS OFFICIAL_RATING
         , '2017-07-20' :: DATE AS RATING_DATE   
    UNION ALL
    SELECT 3 AS FINISH_POSITION
         , '48' AS OFFICIAL_RATING
         , '2017-07-29' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 1 AS FINISH_POSITION
         , '58' AS OFFICIAL_RATING
         , '2017-08-15' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 2 AS FINISH_POSITION
         , '60' AS OFFICIAL_RATING
         , '2017-08-20' :: DATE AS RATING_DATE   
    UNION ALL
    SELECT 1 AS FINISH_POSITION
         , '48' AS OFFICIAL_RATING
         , '2017-08-22 ' :: DATE AS RATING_DATE   
    UNION ALL
    SELECT 9 AS FINISH_POSITION
         , '52' AS OFFICIAL_RATING
         , '2017-08-27' :: DATE AS RATING_DATE
    UNION ALL
    SELECT 2 AS FINISH_POSITION
         , NULL AS OFFICIAL_RATING
         , '2017-09-05':: DATE AS RATING_DATE   
    UNION ALL
    SELECT 1 AS FINISH_POSITION
         , '48' AS OFFICIAL_RATING
         , '2017-09-17':: DATE AS RATING_DATE   
    UNION ALL
    SELECT 1 AS FINISH_POSITION
         , '51' AS OFFICIAL_RATING
         , '2017-09-21':: DATE AS RATING_DATE   
    UNION ALL
    SELECT 11 AS FINISH_POSITION
         , '60' AS OFFICIAL_RATING
         , '2017-09-25':: DATE AS RATING_DATE       
    UNION ALL
    SELECT 13 AS FINISH_POSITION
         , '63' AS OFFICIAL_RATING
         , '2017-09-30':: DATE AS RATING_DATE       
    UNION ALL
    SELECT 14 AS FINISH_POSITION
         , '61' AS OFFICIAL_RATING
         , '2017-10-04':: DATE AS RATING_DATE    
    UNION ALL
    SELECT 7 AS FINISH_POSITION
         , '49' AS OFFICIAL_RATING
         , '2017-10-05':: DATE AS RATING_DATE       
    UNION ALL
    SELECT 9 AS FINISH_POSITION
         , NULL AS OFFICIAL_RATING
         , '2017-10-09':: DATE AS RATING_DATE       
    UNION ALL
    SELECT 11 AS FINISH_POSITION
         , '60' AS OFFICIAL_RATING
         , '2017-10-15':: DATE AS RATING_DATE      
)

SELECT T3.FINISH_POSITION AS FINISH_POSITION
     , T3.OFFICIAL_RATING AS OFFICIAL_RATING
     , T3.DATE AS DATE
     , CASE WHEN T3.RATING_OF_1 IS NULL OR T3.OFFICIAL_RATING IS NULL THEN 0
            ELSE CAST(T3.RATING_OF_1 AS INT) - CAST(T3.OFFICIAL_RATING AS INT) 
       END AS DIFFERENCE
FROM 
(

    SELECT L.FINISH_POSITION AS FINISH_POSITION
         , L.OFFICIAL_RATING AS OFFICIAL_RATING
         , L.RATING_DATE AS DATE
         , L.OFFICIAL_RATING AS RATING_OF_1
    FROM LOG AS L
    WHERE L.RATING_DATE < (SELECT MIN(RATING_DATE) 
                           FROM LOG 
                           WHERE FINISH_POSITION = 1) 
    UNION

    SELECT L.FINISH_POSITION AS FINISH_POSITION
         , L.OFFICIAL_RATING AS OFFICIAL_RATING
         , L.RATING_DATE AS DATE
         , L.OFFICIAL_RATING AS RATING_OF_1 
    FROM LOG AS L
    WHERE OFFICIAL_RATING IS NULL

    UNION

    SELECT L.FINISH_POSITION AS FINISH_POSITION
         , L.OFFICIAL_RATING AS OFFICIAL_RATING
         , L.RATING_DATE AS DATE
         , T2.RATING_OF_1 AS RATING_OF_1
    FROM LOG AS L
    LEFT JOIN
    (
        SELECT T1.OFFICIAL_RATING_OF_1 AS RATING_OF_1
             , T1.DATE AS DATE
             , LEAD(T1.DATE) OVER (ORDER BY T1.DATE ASC) AS NEXT_RATEING_OF_1
        FROM (
                 SELECT FINISH_POSITION
                      , OFFICIAL_RATING AS OFFICIAL_RATING_OF_1        
                      , RATING_DATE AS DATE
                 FROM LOG
                 WHERE FINISH_POSITION = 1
              ) AS T1
         WHERE T1.OFFICIAL_RATING_OF_1 IS NOT NULL 
    ) AS T2
    ON L.RATING_DATE > T2.DATE AND L.RATING_DATE < T2.NEXT_RATEING_OF_1 
    WHERE OFFICIAL_RATING IS NOT NULL AND T2.NEXT_RATEING_OF_1 IS NOT NULL

    UNION
    SELECT FINISH_POSITION AS FINISH_POSITION
         , OFFICIAL_RATING AS OFFICIAL_RATING       
         , RATING_DATE AS DATE
         , OFFICIAL_RATING AS OFFICIAL_RATING_OF_1
    FROM LOG
    WHERE FINISH_POSITION = 1
) AS T3

UNION 

SELECT L.FINISH_POSITION AS FINISH_POSITION
     , L.OFFICIAL_RATING AS OFFICIAL_RATING
     , L.RATING_DATE AS DATE
     , CASE WHEN OFFICIAL_RATING IS NULL THEN 0 
            ELSE CAST(T3.RATING_OF_1 AS INT) - CAST(L.OFFICIAL_RATING AS INT) 
       END AS DIFFERENCE
FROM LOG AS L
RIGHT OUTER JOIN 
(
    SELECT DISTINCT
           T2.RATING_OF_1
         , T2.DATE AS DATE
         , T2.NEXT_RATEING_OF_1
    FROM 
    (
        SELECT T1.OFFICIAL_RATING_OF_1 AS RATING_OF_1
             , T1.DATE AS DATE
             , LEAD(T1.DATE) OVER (ORDER BY T1.DATE ASC) AS NEXT_RATEING_OF_1
        FROM (
                 SELECT FINISH_POSITION
                      , OFFICIAL_RATING AS OFFICIAL_RATING_OF_1        
                      , RATING_DATE AS DATE
                 FROM LOG
                 WHERE FINISH_POSITION = 1
             ) AS T1
    ) AS T2
    WHERE T2.NEXT_RATEING_OF_1 IS NULL
) AS T3
ON L.RATING_DATE > T3.DATE

ORDER BY DATE ASC
;

Output:

Please note that you will have to remove the WITH clause and change the table & column names in order to run against your table. For example, the table I created is called LOG. Also the code is PostgreSQL-based so there are syntax limitations compared to other versions of SQL.

Answer 3

You don't mention which version of MySQL you are using. In MySQL 8.x you can use the LAG() function. For example:

select
  *,
  official_rating - lag(official_rating) over(order by date) as difference
from t
order by date

See 12.21.1 Window Function Descriptions.