shavit - 8 months ago 30

SQL Question

My front-end (SourcePawn) currently does the following:

`float fPoints = 0.0;`

float fWeight = 1.0;

while(results.FetchRow())

{

fPoints += (results.FetchFloat(0) * fWeight);

fWeight *= 0.95;

}

In case you don't understand this code, it goes through the resultset of this query:

`SELECT points FROM table WHERE auth = 'authentication_id' AND points > 0.0 ORDER BY points DESC;`

The resultset is floating numbers, sorted by

`points`

My front-end takes the 100% of the first row, then 95% of the second one, and it drops by 5% every time. It all adds up to

`fPoints`

What I'm looking for, is a solution of how to replicate this code in pure SQL and receive the sum which is called

`fPoints`

I'm very lost. I don't know where to start and guidance of any kind would be very nice.

Answer

You can do this using variables:

```
SELECT points,
(points * (@f := 0.95 * @f) / 0.95) as fPoints
FROM table t CROSS JOIN
(SELECT @f := 1.0) params
WHERE auth = 'authentication_id' AND points > 0.0
ORDER BY points DESC;
```

A note about the calculation. The value of `@f`

starts at 1. Because we are dealing with variables, the assignment and the use of the variable need to be in the same expression -- MySQL does not guarantee the order of evaluation of expressions.

So, the `0.95 * @f`

reduces the value by 5%. However, that is for the *next* iteration. The `/ 0.95`

undoes that to get the right value for *this* iteration.