2 
select historic_betfair_win_prices.sf_name,historic_runners.finish_position,historic_runners.official_rating,historic_betfair_win_prices.date, historic_betfair_win_prices.bsp,
         if(finish_position = 1 , @b:=@b+1,@b:=@b) opening_position,
         if(finish_position = 1 , @last_op:=official_rating,@last_op:=@last_op)last_opening_position,
         cast(if(@b = 0 or finish_position = 1,0,@last_op - official_rating) as decimal(10,2)) diff
from historic_runners
inner join historic_betfair_win_prices on historic_betfair_win_prices.runner_id = historic_runners.runner_id and historic_betfair_win_prices.sf_race_id = historic_runners.race_id
cross join (select @b:=0, @last_op :=0) b
where historic_betfair_win_prices.sf_name = "Camanche Grey"
limit 50

I want to hide

opening_position, last_opening_position

columns from the output result. My Mysql version 8.0.18

Improve this question
2
  • Remove them, and move the calculations into diff field expression. Commented Feb 19, 2020 at 6:32
  • @ZajjithVedha . . . I would suggest that you ask a new question. You can do what you want without variables -- and they are deprecated now in MySQL. Provide sample data, desired results, and an explanation of the logic you want to implement. Commented Feb 19, 2020 at 12:52

2 Answers 2

Reset to default
1

Use a subquery 

select sf_name,finish_position,official_rating,date,bsp,diff from
(
select historic_betfair_win_prices.sf_name,historic_runners.finish_position,historic_runners.official_rating,historic_betfair_win_prices.date, historic_betfair_win_prices.bsp,
         if(finish_position = 1 , @b:=@b+1,@b:=@b) opening_position,
         if(finish_position = 1 , @last_op:=official_rating,@last_op:=@last_op)last_opening_position,
         cast(if(@b = 0 or finish_position = 1,0,@last_op - official_rating) as decimal(10,2)) diff
from historic_runners
inner join historic_betfair_win_prices on historic_betfair_win_prices.runner_id = historic_runners.runner_id and historic_betfair_win_prices.sf_race_id = historic_runners.race_id
cross join (select @b:=0, @last_op :=0) b
where historic_betfair_win_prices.sf_name = "Camanche Grey"
limit 50
)A
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
select historic_betfair_win_prices.sf_name,
       historic_runners.finish_position,
       historic_runners.official_rating,
       historic_betfair_win_prices.date, 
       historic_betfair_win_prices.bsp,
       cast(if((@b := if(finish_position = 1 , 
                        @b+1, 
                        @b))= 0 or finish_position = 1,
               0,
               (@last_op := if(finish_position = 1 , 
                              official_rating, 
                              @last_op)) - official_rating) as decimal(10,2)) difffrom historic_runners
inner join historic_betfair_win_prices on historic_betfair_win_prices.runner_id = historic_runners.runner_id and historic_betfair_win_prices.sf_race_id = historic_runners.race_id
cross join (select @b:=0, @last_op :=0) b
where historic_betfair_win_prices.sf_name = "Camanche Grey"
limit 50
Improve this answer

3 Comments

From your code result are some of diff values are changed to 0.00 and some of them changed to NULL. So diff column has only 0.00 and NULL. But I got different values from my above code.
@ZajjithVedha Yes, I was wrong during insertion - I'll edit. some of them changed to NULL I do not see the conditions which may cause assigning NUUL into any variable.
@ZajjithVedha Edited, check.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.