I have two tables
This is a scoring / qualifier list query where I'm asked to provide a list of Players where 1 in 15 in an event qualify. I.e. If there are 15-29 1 person will qualify, 30-44 2 people etc. Based on the Rank 1 being first place, 2 second, etc.
I'm asked to provide a list of Players where they have qualified.
The other requirement is that the Player must have played in a minimum of 20 events.
I'm aware of "top" however I can't see how I could have the top value dynamic based on the number of players in the event.
Player Table
ID NAME 1 player1 2 player2 3 player3 4 player4 5 player5 6 player6 7 player7 8 player8 9 player9 10 player10 11 player11 12 player12 13 player13 14 player14 15 player15 16 player16 17 player17 18 player18 19 player19 20 player20 21 player21 22 player22 23 player23 24 player24 25 player25 26 player26 27 player27 28 player28 29 player29 30 player30 31 player31 32 player32 33 player33 34 player34 35 player35 36 player36 37 player37 38 player38 39 player39 40 player40 41 player41 42 player42 43 player43 44 player44 45 player45 46 player46 47 player47
Event Table
EVENTID PLAYERID RANK 1 2 10 1 10 9 1 3 2 1 1 15 1 4 1 1 5 16 1 41 4 1 30 3 1 6 6 1 11 7 1 12 5 1 13 8 1 30 11 1 29 14 1 28 13 1 7 12 2 1 1 2 2 31 2 3 30 2 4 29 2 5 24 2 6 3 2 7 4 2 8 5 2 9 28 2 10 27 2 11 26 2 12 6 2 13 7 2 14 8 2 15 9 2 16 10 2 17 11 2 18 12 2 40 13 2 41 14 2 18 15 2 20 16 2 21 17 2 22 18 2 23 19 2 24 20 2 25 21 2 26 22 2 30 23 2 31 2 2 32 25
Expected Result (without 20 games filter)
player4 player1 player31
Thanks in advance.
You can do it this way (without the second requirement)
SELECT p.name
FROM
(
SELECT eventid, COUNT(*) total
FROM event
GROUP BY eventid
) t JOIN event e
ON t.eventid = e.eventid
AND e.rank <= FLOOR(t.total / 15) JOIN player p
ON e.playerid = p.id
Output:
| NAME | |----------| | player1 | | player4 | | player31 |
Here is SQLFiddle demo
UPDATE you can meet your second requirement this way
SELECT p.name,
IF((
SELECT COUNT(DISTINCT eventid)
FROM event
WHERE playerid = e.playerid
) >= 20, 'Yes', 'No') eligible
FROM
(
SELECT eventid, COUNT(*) total
FROM event
GROUP BY eventid
) t JOIN event e
ON t.eventid = e.eventid
AND e.rank <= FLOOR(t.total / 15) JOIN player p
ON e.playerid = p.id
Sample output:
| NAME | ELIGIBLE | |----------|----------| | player1 | No | | player4 | No | | player31 | No |
Here is SQLFiddle demo