Noman uddin Noman uddin - 4 months ago 9
SQL Question

MySQL, Adding two columns recursively after comparing them

This is my table data:

ID val Coulmn1 Column2
1 1 0.4000 0
1 10 0.1250 0
1 18 0.1429 0
1 26 0.2500 0
2 13 0.0000 0
2 21 0.1429 0
2 29 0.2500 0
2 34 0.3333 0
3 6 0.3333 0
3 7 0.20 0
3 14 0.2500 0
3 22 0.1429 0
3 33 0.2500 0
4 8 0.2000 0
4 15 0.1250 0
4 23 0.1429 0
4 31 0.0000 0


I want to add to columns if column1 is greater than column2 in the same row and if the final value is max it should be applied to all the rows of the same id.

I have following scenario.

if (Column1 > Column1 )
then (Column1 + Column1 )
else (Column1 )


Output is Can be:

ID val Coulmn1 Column2
1 1 0.4000 0.4000
1 10 0.1250 0.4000
1 18 0.1429 0.4000
1 26 0.2500 0.4000
2 13 0.0000 0.39285
2 21 0.1429 0.39285
2 29 0.2500 0.39285
2 34 0.3333 0.39285
3 6 0.3333 0.3333
3 7 0.20 0.3333
3 14 0.2500 0.3333
3 22 0.1429 0.3333
3 33 0.2500 0.3333
4 8 0.2000 0.2
4 15 0.1250 0.2
4 23 0.1429 0.2
4 31 0.0000 0.2


any supportive link or solution
Thanks in advance

Answer

Try this:

select
    t1.id, t1.val, t1.column1,
    t2.col as column2
from yourtable t1
join (
    select 
        max(tmp.col) as col, 
        id
   from (
       select 
           yourtable.*,
           @col := case when @grp = id then 
                        case when @col < column1 then @col + column1 else @col end
                        else column1 end as col,
           @grp := id
       from yourtable
       cross join (select @col := 0, @grp := null) t
       order by id, val
   ) tmp
   group by id
) t2 on t1.id = t2.id

This is almost for all databases.

And here is demo for mysql:SQLFiddle Demo

Comments