Member Avatar for Member #210412

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

  • 1 Contributor
  • 1 Reply
  • 290 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Member #210412

Dani AI

Generated

Short expert note for : the goal is to compare each row with the next one (1 vs 2, 2 vs 3, ...). The loop in your post is fragile because it assumes contiguous IDs and updates the control variables in a way that loses the intended “previous row” values. Two safer, SQL-only approaches follow — pick the one that matches your MySQL version.

If you run MySQL 8 or later (recommended): use window functions (LAG) so each row can see the previous row’s values without loops.

WITH t AS (
  SELECT MyID, Name, Game, Total, High, Low,
         Total / NULLIF(Game,0) AS average
  FROM mytable
)
SELECT MyID, Name, Game, Total, High, Low, average,
       (average - LAG(average) OVER (ORDER BY MyID)) * LAG(Game) OVER (ORDER BY MyID) AS PointFromBelow,
       average + (average - LAG(average) OVER (ORDER BY MyID)) * LAG(Game) OVER (ORDER BY MyID) AS Whole
FROM t
ORDER BY Whole DESC, Name ASC;

If you’re on older MySQL (pre-8), use a stable ORDERED subquery plus user variables to carry the previous row’s values into the next row:

SET @prev_avg := NULL, @prev_game := NULL;

SELECT MyID, Name, Game, Total, High, Low, average, PointFromBelow, Whole
FROM (
  SELECT s.MyID, s.Name, s.Game, s.Total, s.High, s.Low, s.avg AS average,
         IF(@prev_avg IS NULL, NULL, (s.avg - @prev_avg) * @prev_game) AS PointFromBelow,
         IF(@prev_avg IS NULL, NULL, s.avg + (s.avg - @prev_avg) * @prev_game) AS Whole,
         @prev_avg := s.avg AS __pv, @prev_game := s.Game AS __pg
  FROM (
    SELECT MyID, Name, Game, Total, High, Low, Total / NULLIF(Game,0) AS avg
    FROM mytable
    ORDER BY MyID
  ) AS s
) AS q;

Quick tips and cautions:

  • Use NULLIF(Game,0) to avoid division-by-zero. Consider CAST(... AS DECIMAL(...)) if you need fixed precision.
  • Don’t assume MyID+1 yields the “next” row — use ordered queries or MIN(MyID) > current if you must find the next ID.
  • Prefer the window-function solution when available: it’s clearer, safer, and faster on larger tables.
  • Test with a SELECT first before attempting any UPDATE; add an index on MyID for performance.

If you still want a stored-procedure loop, iterate by repeatedly selecting the next MyID with MIN(MyID) WHERE MyID > current and stop when NULL — that avoids gaps and the cursor-reset bug you were seeing.

Member Avatar for Member #210412

i think no one knows sql very well...

just basics ??

commented: use google or read a book little boy -2
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.