How do I get the sum of each persons best ten scores of the season?

I have a database of results for a season of 开发者_JS百科25 games. However only each persons best ten scores count. Can anybody tell me how to sum just the top ten scores of each person AND show the minimum score that was used in that sum (their tenth best). The database has PlayerName, TournamentID, Points eg.

 - TounamentID  PlayerName  Points
 - 1             Jo           100
 - 1             Tel          50
 - 1             Kevin        75
 - 2             Jo           100
 - 2             Tel          50
 - 2             Kevin        75
 - 3             Jo           100
 - 3             Tel          50
 - 3             Kevin        75
 - 4             Jo           100
 - 4             Tel          50
 - 4             Kevin        75
 - 5             Jo           100
 - 5             Tel          50
 - 5             Kevin        75 etc

Many thanks in advance

EDIT 1

At the moment I have this kind of working though it doesn't handle duplicate scores very well and can actual end up adding up the top 11 if there's a duplicate;

SELECT X.PlayerName, Sum(X.Points) AS SumOfPoints, Min(X.Points) AS Target
FROM SoP11PreBats AS X
WHERE (((10)>(SELECT count(*)
       FROM SoP11PreBats
      WHERE PlayerName = X.PlayerName
        AND Points > X.Points )))
GROUP BY X.PlayerName
ORDER BY Sum(X.Points) DESC;

---

Something like this would work for one player at a time:

SELECT SUM(n), MIN(n) FROM
  (SELECT   points AS n
   FROM     table
   WHERE    PlayerName = ?
   ORDER BY n DESC
   LIMIT    10
  )

I'm not sure how to expand it to produce a table for every player.

---

SELECT test.playername, sum(top10.score), MIN(top10.score)
FROM test
LEFT JOIN (SELECT playername, score FROM test ORDER BY score DESC LIMIT 10) top10
ON top10.playername = test.playername
GROUP BY test.playername

Edit: Turns out the above approach using a subselect and join is not going to do the trick. Because you limit the results in the subselect it is not going to lead to a set of max 10 records PER playername.
The next approach would be to do something like

SELECT pk, name, score from test where
pk IN (SELECT pk FROM test t2 WHERE t2.name = name ORDER BY score DESC LIMIT 10)

This could create the proper set of records to join with. However LIMIT is not supported inside an IN clause (yet). So this won't compile.

EIDT2: The final approach I can think of goes like this:

select distinct test.name
, (SELECT sum(t2.score) FROM (select t3.score FROM test t3 WHERE t3.name = test.name ORDER BY score desc LIMIT 10) t2)
, (SELECT min(t2.score) FROM (select t3.score FROM test t3 WHERE t3.name = test.name ORDER BY score desc LIMIT 10) t2)
FROM test

This one also won't compile because the most inner test.name in the where can't be resolved properly.

I don't think that what you want to do can currently be done in 1 single query on mysql, but I'm curious if someone can proof me wrong.

---

Ok I got it working but it's about the most nasty sql hack I could think of and I'm not sure you should even consider to put it in production like this. It also only runs on mysql:

select PlayerName, sum(Points), min(Points) from 
(select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 1,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 2,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 3,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 4,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 5,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 6,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 7,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 8,1) as Points FROM test 
UNION ALL
select distinct SoP11PreBats.PlayerName, (SELECT t2.Points FROM test t2 WHERE t2.PlayerName = SoP11PreBats.PlayerName ORDER BY Points desc LIMIT 9,1) as Points FROM test 
) top10
group by name

---

I have a feeling this will not work:

SELECT *
FROM
  ( SELECT pd.PlayerName
         , ( SELECT SUM(t10.Points)
             FROM
               ( SELECT t10.Points
                 FROM SoP11PreBats AS t10
                 WHERE t10.PlayerName = pd.PlayerName
                 ORDER BY t10.Points DESC
                 LIMIT 10
               ) AS x
           ) AS Sum10
         , ( SELECT t10.Points
             FROM SoP11PreBats AS t10
             WHERE t10.PlayerName = pd.PlayerName
             ORDER BY t10.Points DESC
             LIMIT 1 OFFSET 9
           ) AS TenthBest
    FROM 
        ( SELECT DISTINCT PlayerName
          FROM SoP11PreBats
        ) AS pd
  ) AS y
ORDER BY Sum10 DESC

---

But this will:

SELECT pb.PlayerName  AS PlayerName  
     , COALESCE(SUM(p.Points),0) + pb.TenthBest*(10-COUNT(p.Points))
                      AS SumOfPoints
     , pb.TenthBest   AS Target
FROM
  ( SELECT pd.PlayerName
         , ( SELECT t10.Points
             FROM SoP11PreBats AS t10
             WHERE t10.PlayerName = pd.PlayerName
             ORDER BY t10.Points DESC
             LIMIT 1 OFFSET 9
           ) AS TenthBest
    FROM 
        ( SELECT DISTINCT PlayerName
          FROM SoP11PreBats
        ) AS pd
  ) AS pb
  LEFT JOIN SoP11PreBats AS p
    ON  p.PlayerName = pb.PlayerName
    AND p.Points > pb.TenthBest
GROUP BY pb.PlayerName
ORDER BY SumOfPoints DESC

---

This works (see test output below):

set @count:=0, @player:='';
SELECT 
    PlayerName,
    SUM(Points) as sum_top_10, 
    MIN(Points) as min_top_10
FROM (SELECT PlayerName, Points
    FROM (SELECT
    Points, 
        @count := if (@player != PlayerName, 0, @count + 1) as count, 
        @player := PlayerName as PlayerName
        FROM (SELECT PlayerName, Points FROM SoP11PreBATS order by 1, 2 desc) x) y
    where count < 10) z
group by 1;

Here's the test, using OP's data, plus extra rows for 'Jo' to make more than 10 rows:

create table SoP11PreBATS (TounamentID int, PlayerName text,  Points int);
delete from SoP11PreBATS;
insert into SoP11PreBATS values
(1, 'Jo', 100), (1, 'Tel', 50), (1, 'Kevin', 75), (2, 'Jo', 100), (2, 'Tel', 50),
(2, 'Kevin', 75), (3, 'Jo', 100), (3, 'Tel', 50), (3, 'Kevin', 75), (4, 'Jo', 100),
(4, 'Tel', 50), (4, 'Kevin', 75), (5, 'Jo', 100), (5, 'Tel', 50), (5, 'Kevin', 75),
(5, 'Jo', 50), (6, 'Jo', 75), (7, 'Jo', 100), (8, 'Jo', 50), (9, 'Jo', 75),
(10, 'Jo', 50), (11, 'Jo', 75), (12, 'Jo', 100);
select * from SoP11PreBATS where playername = 'Jo' order by points desc;
+-------------+------------+--------+
| TounamentID | PlayerName | Points |
+-------------+------------+--------+
|           1 | Jo         |    100 |
|           2 | Jo         |    100 |
|           3 | Jo         |    100 |
|           4 | Jo         |    100 |
|           5 | Jo         |    100 |
|           7 | Jo         |    100 |
|          12 | Jo         |    100 |
|           6 | Jo         |     75 |
|           9 | Jo         |     75 |
|          11 | Jo         |     75 |
|           5 | Jo         |     50 |
|           8 | Jo         |     50 |
|          10 | Jo         |     50 |
+-------------+------------+--------+
-- Inspection shows Jo should have 925 as sum and 75 as min
-- Ran query above and got:
+------------+------------+------------+
| PlayerName | sum_top_10 | min_top_10 |
+------------+------------+------------+
| Jo         |        925 |         75 |
| Kevin      |        375 |         75 |
| Tel        |        250 |         50 |
+------------+------------+------------+
-- Test output correct

---

I think this works, and it only uses one derived table:

  SELECT @row := 0, @pp := NULL, @min := 0;
  SELECT Player,
         SUM(Points) AS Points,
         MIN(Points) AS MinPoints
    FROM (
  SELECT Player,
         Points,
         @row := IF(
           IFNULL(@pp, '') <> Player AND NOT (@pp := Player),
           1,
           @row + 1
         ) AS Row
    FROM SoP11PreBats
ORDER BY Player, Points DESC
) tmp
   WHERE tmp.Row <= 10
GROUP BY Player;

Test data:

CREATE TABLE `SoP11PreBats` (
 `TournamentID` int(11) NOT NULL,
 `Player` varchar(255) NOT NULL,
 `Points` int(11) NOT NULL
);

INSERT INTO SoP11PreBats (TournamentID, Player, Points) VALUES
(15, 'Jo',     10),
(14, 'Jo',     20),
(13, 'Jo',     30),
(12, 'Jo',     40),
(11, 'Jo',     50),
(10, 'Jo',     60),
( 9, 'Jo',     70),
( 8, 'Jo',     80),
( 7, 'Jo',     90),
( 6, 'Jo',    100),
( 5, 'Jo',    110),
( 4, 'Jo',    120),
( 3, 'Jo',    130),
( 2, 'Jo',    140),
( 1, 'Jo',    150),
( 1, 'Tel',    15),
( 2, 'Tel',    25),
( 3, 'Tel',    35),
( 4, 'Tel',    45),
( 5, 'Tel',    55),
( 6, 'Tel',    65),
( 7, 'Tel',    75),
( 8, 'Tel',    85),
( 9, 'Tel',    95),
(10, 'Tel',   105),
(11, 'Tel',   115),
(12, 'Tel',   125),
(13, 'Tel',   135),
(14, 'Tel',   145),
(15, 'Tel',   155),
( 1, 'Kevin',  10),
( 2, 'Kevin',  20),
( 3, 'Kevin',  30),
( 4, 'Kevin',  40),
( 5, 'Kevin',  50),
( 6, 'Kevin',  60),
( 7, 'Kevin',  70),
( 8, 'Kevin',  80),
( 9, 'Kevin',  90),
(10, 'Kevin', 100),
(11, 'Kevin', 110),
(12, 'Kevin', 120),
(13, 'Kevin', 130),
(14, 'Kevin', 140),
(15, 'Kevin', 150);

Result:

+--------+--------+-----------+
| Player | Points | MinPoints |
+--------+--------+-----------+
| Jo     |   1050 |        60 |
| Kevin  |   1050 |        60 |
| Tel    |   1100 |        65 |
+--------+--------+-----------+