i wanna put 1st row in action with 2nd row... then 2nd with 3rd and go on...
i did this three records i got correct result but when i add more records... everything is involved... i tried many different combinations but no success..
i want to solve this in sql not with php or asp... ( which easiest way for me )
Table prop:
MyID (int), Name (char), Game(int), Total(int), High(int), Low(int)
Records:
1 AAA 16 641 49 31
2 BBB 16 636 45 32
3 CCC 18 711 49 33
...
begin
declare min_rec_id, max_rec_id, total_record, cursore int;
set min_rec_id = (select min(MyID) from mytable);
set max_rec_id = ( select max(MyID) from mytable );
set total_record = ( select count(*) from mytable);
set cursore = 0;
while min_rec_id <= max_rec_id do
set min_rec_id = cursore + 1;
select MyID, Name, Game, Total, High, Low,
( Total / Game ) as Avarage,
( (( Total / Game ) - ( select Total / Game from mytable where MyID = min_rec_id ) ) * ( select Game from mytable where MyID = min_rec_id ) ) as PointFromBelow,
( ( Total / Game ) + ( ( ( Total / Game ) - ( select Total / Game from mytable where MyID = min_rec_id ) ) * ( select Game from mytable where MyID= min_rec_id ) ) ) as Whole
from mytable ORDER BY WHOLE DESC, Name ASC;
end while;
end