Show biggest margin between 1st and 2nd within group

Name   Day   Points
Brian     1        6
Tom       1       11
Freddy    1        7
Kim       2       10
Sandr开发者_高级运维a    2        1
Brian     2        3

I need to know who has won with the biggest margin to number two - but only between people on the same day.

Thus if done properly it would tell me Kim has won by the biggest margin.

I don't quite know how to handle on this one.

---

select 
  first_place.name, 
  max_points-max(points) as max_margin 
from the_table
inner join 
  (select name, day, max(points) as max_points 
   from the_table group by day) as first_place 
on the_table.day=first_place.day 
where the_table.points<max_points 
group by the_table.day 
order by max_margin desc limit 1 ;

---

This would need to be done with two sub queries... Inner most to get the highest score for a single day, then, find the next highest scrore under the first place position, then find the margin... However, due to your sample data of just names, no consideration for unique names which would otherwise be by some internal ID... Say "Brian" in your sample data... is it the same Brian on both days, or is it a different person. Additionally, what if two people are tied for first place with 11 points, then my query would show BOTH people in first place before the margin to the now "3rd" place person as the detected margin. You will probably have to modify some to accommodate such conditions described..

SELECT 
      FS.Day, 
      FS.FirstPlace, 
      FS.SecondPlace, 
      FS.FirstPlace - FS.SecondPlace as Margin,
      G.Name
   FROM 
      ( SELECT G2.Day, 
               FirstPlace.FirstPlacePoints FirstPlace, 
               MAX( G2.Points ) as SecondPlace 
            FROM
                Games G2,
                ( SELECT Day, 
                         MAX( Points ) as FirstPlacePoints
                      FROM 
                         Games
                      GROUP BY 
                         Day ) FirstPlace
            WHERE
                    G2.Day = FirstPlace.Day 
                AND G2.Points < FirstPlace.FirstPlacePoints
            GROUP BY
                1, 2 ) as FS,
        Games G 
   WHERE
          FS.Day = G.Day
      and FS.FirstPlace = G.Points
   ORDER BY
       Margin desc
   LIMIT 1