mysql - get closest value to correct value return zero to many depending on result

Question

My site allows users to guess the result of a sports match. At the end of the match the guesses should be compared to the actual result. The winner(s) are the members with the closest correct guess

Im looking for a way to return all members who guessed the correct result and score difference IF NO (zero) member guessed correctly return members who guessed closest to the correct result

See MYSQL FIDLE EXAMPLE

I modified the script to change fixed values taking variables as you can see below

if(isset($_POST['resultBtn'])){
    foreach($_POST['winner'] as $id =>$winner){
        $winScore = $_POST['score'][$id];
        :
        :


$sql="SELECT p.*
        FROM Multiple_Picks p
        WHERE p.event_id='$matchId' AND 
        p.pick='$winner' AND
        abs(p.score-'$winScore') = (SELECT min(abs(p2.score-1))
                        FROM Multiple_Picks p2
                        Where p2.pick=p.pick AND
                        p2.event_id = p.event_id)";

My problem is if I run this script on the following table:

NOTHING gets displayed even if I put result exactly correct:

My variable values are correct in the sql statment so that is not the problem

Any help will be welcomed...

IMPORTANT THE USER WHO SELECTED CLOSEST CORRECT RESULTS, FOR ALL GAME, DURING THE ROUND IS THE WINNER

example: if user A won 4 of the picks and user B won 5 of the picks then user B is the winner of the round

Answer 1

In this answer I call a "Best" pick any pick that has chosen the correct winner for a particular match, and has the closest score to the actual match score.

These scripts also respect the different "rounds" in the competition, since that is an important complication.

This answer comes in two parts: first a query that is similar to the one in the question that returns all the "Best" picks for a particular match. To make it easier to run in SQL Fiddle, I have used MySQL variables instead of PHP variables.

Schema with test data:

create table Multiple_Picks (
  pick_id int,
  member_nr int,
  event_id int,
  pick varchar(100),
  score int
  );

insert into Multiple_Picks 
values
(11,100,1,'Crusaders',15),
(12,100,2,'Waratahs',10),
(13,100,3,'Chiefs',4),

(21,200,1,'Crusaders',15),
(22,200,2,'Waratahs',10),
(23,200,3,'Lions',4),

(31,300,1,'Crusaders',15),
(32,300,2,'Waratahs',12),
(33,300,3,'Lions',6),

(41,100,4,'Crusaders',20),
(42,100,5,'Waratahs',20),
(43,100,6,'Lions',20)
;

Queries to show all picks and then best picks for a particular match:

set @matchId = 2;
set @winner = 'Waratahs';
set @winScore = 8;

-- Show all picks for a particular match
select * from Multiple_Picks
where event_id = @matchId;

-- Show best picks for a particular match
select p.*
from Multiple_Picks p
where p.event_id = @matchId
and p.pick = @winner
and abs(p.score - @winScore) = 
  (select min(abs(other.score - @winScore)) 
   from Multiple_Picks other
   where other.event_id = @matchId
   and other.pick = @winner
  )
;

SQL Fiddle to show picks for particular match

-- Show all picks for a particular match
+---------+-----------+----------+----------+-------+
| pick_id | member_nr | event_id |   pick   | score |
+---------+-----------+----------+----------+-------+
|      12 |       100 |        2 | Waratahs |    10 |
|      22 |       200 |        2 | Waratahs |    10 |
|      32 |       300 |        2 | Waratahs |    12 |
+---------+-----------+----------+----------+-------+

-- Show best picks for a particular match
+---------+-----------+----------+----------+-------+
| pick_id | member_nr | event_id |   pick   | score |
+---------+-----------+----------+----------+-------+
|      12 |       100 |        2 | Waratahs |    10 |
|      22 |       200 |        2 | Waratahs |    10 |
+---------+-----------+----------+----------+-------+

Now we need to work towards finding the winner of each round of the competition.

First we have extra test data that contains the actual scores for Matches in rounds 1 and 2.

create table Matches (
  event_id int,
  winner varchar(100),
  score int,
  round int
  );

insert into Matches 
values
(1,'Crusaders',10,1),
(2,'Waratahs',11,1),
(3,'Lions',4,1),

(4,'Crusaders',20,2),
(5,'Waratahs',20,2),
(6,'Chiefs',20,2)
;

Now select the best picks for all Matches. The subselect (aliased as m) calculates best_diff for each match as the minimum difference between the actual score and every guessed score. This subselect is then joined to every pick so that only "Best" picks are returned.

-- Show all best picks for all Matches
select p.*, m.round
from Multiple_Picks p
join (
  select m2.event_id, m2.winner, m2.score, m2.round, 
         min(abs(m2.score-p2.score)) as best_diff
  from Matches m2
  join Multiple_Picks p2
  on p2.event_id = m2.event_id and p2.pick = m2.winner
  group by m2.event_id, m2.winner, m2.score, m2.round
) as m
on p.event_id = m.event_id and p.pick = m.winner 
   and abs(m.score - p.score) = m.best_diff
order by m.round, p.event_id
;

It is then easy to get a count of Best picks for each player for each round by just grouping the previous query by member_nr and round:

-- Show a count of best picks for each player for each round
select p.member_nr, m.round, count(*) as best_count
from Multiple_Picks p
join (
  select m2.event_id, m2.winner, m2.score, m2.round, 
         min(abs(m2.score-p2.score)) as best_diff
  from Matches m2
  join Multiple_Picks p2
  on p2.event_id = m2.event_id and p2.pick = m2.winner
  group by m2.event_id, m2.winner, m2.score, m2.round
) as m
on p.event_id = m.event_id and p.pick = m.winner 
   and abs(m.score - p.score) = m.best_diff
group by p.member_nr, m.round
order by m.round, count(*) desc
;

SQL Fiddle for all best picks and counts for all matches

-- Show all best picks for all Matches
+---------+-----------+----------+-----------+-------+-------+
| pick_id | member_nr | event_id |   pick    | score | round |
+---------+-----------+----------+-----------+-------+-------+
|      31 |       300 |        1 | Crusaders |    15 |     1 |
|      21 |       200 |        1 | Crusaders |    15 |     1 |
|      11 |       100 |        1 | Crusaders |    15 |     1 |
|      12 |       100 |        2 | Waratahs  |    10 |     1 |
|      32 |       300 |        2 | Waratahs  |    12 |     1 |
|      22 |       200 |        2 | Waratahs  |    10 |     1 |
|      23 |       200 |        3 | Lions     |     4 |     1 |
|      41 |       100 |        4 | Crusaders |    20 |     2 |
|      42 |       100 |        5 | Waratahs  |    20 |     2 |
+---------+-----------+----------+-----------+-------+-------+

-- Show a count of best picks for each player for each round
+-----------+-------+------------+
| member_nr | round | best_count |
+-----------+-------+------------+
|       200 |     1 |          3 |
|       300 |     1 |          2 |
|       100 |     1 |          2 |
|       100 |     2 |          2 |
+-----------+-------+------------+

The final stage is to select only those players for each round who have the highest number of Best picks. I tried modifying the above queries, but the nesting becomes two confusing, so my solution was to create a few logical views so that the final query can be more easily understood. The views basically encapsulate the logic of the queries I have explained above:

create view MatchesWithBestDiff as
select m.event_id, m.winner, m.score, m.round, 
       min(abs(m.score-p.score)) as best_diff
from Matches m 
join Multiple_Picks p
on p.event_id = m.event_id and p.pick = m.winner
group by m.event_id, m.winner, m.score, m.round
;

create view BestPicks as
select p.*, m.round
from Multiple_Picks p
join MatchesWithBestDiff m
on p.event_id = m.event_id and p.pick = m.winner 
   and abs(m.score - p.score) = m.best_diff
;

create view BestPickCount as
select member_nr, round, count(*) as best_count
from BestPicks
group by member_nr, round
;

So that the query that shows the winners of each round is simply:

-- Show the players with the highest number of Best Picks for each round
select *
from BestPickCount p
where best_count = 
(
    select max(other.best_count)
    from BestPickCount other
    where other.round = p.round
)
order by round
;

SQL Fiddle for players with most Best picks for each round

-- Show the players with the highest number of Best Picks for each round
+-----------+-------+------------+
| member_nr | round | best_count |
+-----------+-------+------------+
|       200 |     1 |          3 |
|       100 |     2 |          2 |
+-----------+-------+------------+

This whole investigation has reminded me how tricky it can be to get SQL to do much manipulation where records need to be selected depending on maximums and sums. Some of these types of queries can be much easier with window functions (the OVER and PARTITION BY clauses), but they are not available in MySQL.

While designing the above queries, I found a few interesting restrictions: MySQL does not allow joins to subqueries in views definitions. ANSI SQL does not allow an aggregate in a subquery to reference both a column from the inner query and a column from the outer query. MySQL seems to sometimes allow this, but I couldn't find clear guidance as to when it is allowed, so I chose to code the above queries to avoid this "feature".

Answer 2

It seems an additional table to store the actual results would help here. E.g let's say this is in a table called results with sample values as follows:

event_id  winner     result
1         Crusaders  16
2         Waratahs   15
3         Chiefs      4
4         Crusaders  17
5         Reds       12
0         Rebels     14
7         Cheetahs   15
8         Crusaders  14

This can then be JOINed on each row and results compared as follows:

SELECT p.*
     , CASE WHEN ABS(p.score - r.result)
                 - CASE WHEN p.pick = r.winner THEN 999999 ELSE 0 END
                 = (SELECT MIN(ABS(p2.score - r2.result)
                               - CASE WHEN p2.pick = r2.winner THEN 999999 ELSE 0 END)
                    FROM picks p2
                    JOIN results r2
                    ON p2.event_id = r2.event_id
                    WHERE p2.event_id = p.event_id)
            THEN 1
            ELSE 0
            END AS win
FROM picks p
JOIN results r
ON p.event_id = r.event_id;

Explanation

The rightmost win column is 1 if the member is calculated to have won or drawn the event, otherwise it is 0. The method used is similar to the one in your post, with the main difference being the team and score are combined. The main thing to be explained here is the 999999, which is subtracted when a correct team is picked - so this can be sure to eclipse the score difference. (Of course, an even bigger value could be picked if needed).

Demo

SQL Fiddle Demo

Answer 3

Best guesser for one match

First find the member(s) who picked the winner and had the closest score guess:

SELECT  p.*
    FROM  
      ( SELECT  MIN(ABS(score-'$winScore')) AS closest
            FROM  Multiple_Picks
            WHERE  event_id = '$matchId'
              AND  pick='$winner' 
      ) AS c
    JOIN  Multiple_Picks p
    WHERE  p.event_id = '$matchId'
      AND  p.pick = '$winner'
      AND  ABS(score-'$winScore') = c.closest 

If that return no results, then what should happen? (It would be because no one picked the winner for a particular event.)

But, I think your question is much more complex. However, the above gives a mapping from (event_id, pick) -> list-of-members who "won". Starting over...

Missing info

There is a mystery -- Where do the event results come from? I will assume this table is already populated:

CREATE TABLE Win (
    event_id ...,   -- which game
    winnner ...,    -- who won
    score ...       -- by what score
)

Best guesser overall

So, create a table of BestGuessers(event_id, member). The details of "all game" and "round" are a bit vague. So I will carry this at least one step further.

CREATE TEMPORARY TABLE BestGuessers(
    event_id ...,
    member_nr ...   -- who guessed the best for that event
)
SELECT  p.event_id, p.member_nr
    FROM  
      ( SELECT  w.event_id, w.winner, MIN(ABS(mp.score-w.score)) AS closest
            FROM  Multiple_Picks AS mp
            JOIN  Win AS w ON mp.event_id = w.event_id
              AND  mp.pick = w.winner
            GROUP BY  w.event_id, w.winner 
      ) AS c
    JOIN  Multiple_Picks p
         ON  p.event_id = c.event_id
        AND  p.pick = c.pick
        AND  p.score = c.closest 

Now, from that, you can pick the best guesser(s).

SELECT  y.member_nr
    FROM  
      ( SELECT  COUNT(*) AS ct
            FROM  BestGuessers
            GROUP BY  member_nr
            ORDER BY  COUNT(*) DESC
            LIMIT  1 
      ) AS x   -- the max number of correct guesses
    STRAIGHT_JOIN  
      ( SELECT  member_nr, COUNT(*) AS ct
            FROM  BestGuessers
            GROUP BY  member_nr
      ) AS y   -- the users who guessed correctly that many times
     USING (ct);

All this is pretty complex; I may have some typos, even logic errors. But maybe I came close.

Answer 4

scenario 1: NO USERS SELECTED THE CORRECT TEAM

I believe that result in this situation should be empty result because everyone has made a mistake.

SCORE RETURN MEMBERS WHO SELECTED THE CLOSEST TO CORRECT SCORE AND RESULT

It seems to be already working in your code example except one mistake in select.

 abs(p.score-'$winScore') = (SELECT min(abs(p2.score-1))

Instead of constant 1 (one) it should be variable '$winScore'

and to control the number of users you get, you may limit your results so you will get something like this:

$sql="SELECT p.*
        FROM Multiple_Picks p
        WHERE p.event_id='$matchId' AND 
        p.pick='$winner' AND
        abs(p.score-'$winScore') = (SELECT min(abs(p2.score-'$winner'))
                        FROM Multiple_Picks p2
                        Where p2.pick=p.pick AND
                        p2.event_id = p.event_id)
order by p.id limit '$numberOfMembers'";

SCENARIO 2: SCENARIO 2: MULTIPLE USERS SELECTED CORRECT TEAM BUT SCORES ARE DIFFERENT RETURN USER(S) WHO GUESSED CLOSEST TO CORRECT SCORE

Same as in the previous question.

SCENARIO 3: MULTIPLE USERS SELECTED CORRECT TEAM AND SCORE RETURN ALL USERS WHO SELECTED CORRECT TEAM AND SCORE

You can achieve this using same query just replace the LIMIT with 'rank' function, and also if you will get several closest scores, but you have to limit their number according to their voting order by id, for this purpose I suggest sorting. So final query will be:

$sql="select * from (SELECT p.*, 
         abs(p.score-'$winScore') scr_diff,
         @rownum := @rownum + 1 rank
        FROM Multiple_Picks p,
            (SELECT @rownum := 0) rank_gen
        WHERE p.event_id='$matchId' AND 
        p.pick='$winner' AND
        abs(p.score-'$winScore') = (SELECT min(abs(p2.score-'$winner'))
                        FROM Multiple_Picks p2
                        Where p2.pick=p.pick AND
                        p2.event_id = p.event_id)
order by p.id
  ) sq
 where sq.scr_diff = 0
    or sq.rank < '$numberOfMembers'";

Fiddle.

Answer 5

Why don't you want just

SELECT p.*, abs(p.score-'$winScore') as diff
        FROM Multiple_Picks p
        WHERE p.event_id='$matchId' AND p.pick='$winner'
        ORDER BY diff ASC 
        LIMIT 1

This will return the closest member for the event. Remove the LIMIT if you need a few of them.

Also, never put your parameters directly into the SQL query, even trusted ones (not your case) and even if you're sure they will always be integer or non-string type. Use prepared statements.