user1542894 - 1 year ago 50

MySQL Question

I have a table

`jackpot`

`uid`

`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

Answer Source

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
```