user1542894 - 1 year ago 57
MySQL Question

# Calculating score based on rank position

I have a table

`jackpot`
with columns
`uid`
for user ID and
`nright`

I manage to SELECT and rank users by right answers, but what next?

``````SELECT
a1.uid,
a1.nright,
COUNT(a2.nright) AS rank
FROM
jackpot a1,
jackpot a2
WHERE
a1.nright < a2.nright
OR (
a1.nright = a2.nright
AND a1.uid = a2.uid
)
GROUP BY
a1.uid,
a1.nright
ORDER BY
a1.nright DESC,
a1.uid DESC
``````

I need to calculate the amount of points to give to each user depending on his position.

Only users with top 3 MAX
`nright`

The total amount of points = the number of users*20.

First position gets 70% of the total, 2nd - 20%, 3rd - 10%.

In case of equal right answers between users, the points are split evenly (50/50, 33/33/33...).

SQL Fiddle

You need to decompose what you want.

1st step : You want the top 3 scores.

``````SELECT nright
FROM jackpot
ORDER BY nright DESC
LIMIT 3
``````

2nd step : The user id who gets this 3 first scores

``````SELECT j.uid
FROM jackpot j
INNER JOIN (
SELECT nright
FROM jackpot
ORDER BY nright DESC
LIMIT 3 ) AS t ON t.nright = j.nright
``````

3rd step: the total amount of point

``````SELECT COUNT(uid)*20 AS lot FROM jackpot
``````

4th step: the rank and the number of person

Here we need to use a variable, as you are in php, you can't use `set @var:= X;` , so the trick is to do a `Select @var:= X` , this variable will not work because of the aggregate functions. So you need to do this :

``````SELECT  @rank := @rank+1 as rank,T1.nright,T1.nb,T1.lot
FROM(
SELECT nright,
COUNT(uid) as nb,
(SELECT COUNT(uid)*20 FROM jackpot) as lot
FROM jackpot
GROUP BY nright
ORDER BY nright DESC
LIMIT 3
)T1, (SELECT @rank:= 0) r
``````

5th step: The lots distribution

`````` SELECT j.uid,
CASE
WHEN t.rank = 1 THEN (t.lot*0.7)/t.nb
WHEN t.rank = 2 THEN (t.lot*0.2)/t.nb
WHEN t.rank = 3 THEN (t.lot*0.1)/t.nb
END as lot

FROM jackpot j
INNER JOIN
(SELECT  @rank := @rank+1 as rank,T1.nright,T1.nb,T1.lot
FROM(
SELECT nright,
COUNT(uid) as nb,
(SELECT COUNT(uid)*20 FROM jackpot) as lot
FROM jackpot
GROUP BY nright
ORDER BY nright DESC
LIMIT 3
)T1, (SELECT @rank:= 0) r) t ON t.nright = j.nright
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download