SQL query to find player with maximum runs

Question

I have the following tables with me :

Table 1 Title : Runs

                    +------------+---------+
                    |    P_ID    |  Runs   |
                    +------------+---------+
                    |      1     | 100     |
                    |      2     | 45      |
                    |      3     | 35      |
                    |      1     | 90      |
                    |      2     | 85      |
                    +------------+---------+

Table 2 Title : Player

                    +------------+---------+
                    |    P_ID    |  Name   |
                    +------------+---------+
                    |    1       | Dhoni   |
                    |    2       | Sehawag |
                    |    3       | Sachin  |
                    |    4       | Dravid  |
                    |    5       | Kohli   |
                    +------------+---------+

I have to find the player name whose sum of runs is maximum.

I am new to MySQL and not able to figure out the correct/most efficient query for that, please help.

Answer 1

With the table of separate runs, making the sums will mean reading entire table each time so for performance it would be good to have the sums precomputed somewhere, maybe even in Player table, with updates either periodically (daily, each hour) or on insert/update in Runs table (trigger, ORM event or other ways).

But that may be not possible for your case, so query to compute them and find the max:

select
  sum(r.runs) total,
  r.p_id,
  p.name
from Runs r
join Player p using(p_id)
group by r.p_id
order by sum(r.runs) desc
limit 1;

Can be seen in action at http://sqlfiddle.com/#!9/27820/2

When two players have the same max, it will pick one of them (probably the one with lower p_id, but that does not have to be true). If it is not OK, define further requirements for this.

It would be good to have index on (p_id, runs) in Runs table so summing per player can be done fast, but then to find the max a filesort is still needed, so if you plan to have hundreds of thousands or millions of rows in Runs table, then you will probably really want to precompute it.

Players table only needs index in p_id, which is easily satisfied by it being primary key which seems quite natural.

Answer 2

Creating sample tables

DROP TABLE Runs;
CREATE TABLE Runs(pid INT , Runs INT);
INSERT INTO Runs VALUES(1, 100),(2, 45 ),(3, 35 ),(1, 90 ),(2, 85 );

DROP TABLE Players;
CREATE TABLE Players(pid INT,Name CHAR(30));
INSERT INTO Players VALUES(1,'Dhoni'),(2,'Sehawag'),(3,'Sachin'),(4,'Dravid'),(5,'Kohli');

MariaDB [world]> SELECT * FROM Players;
+------+---------+
| pid  | Name    |
+------+---------+
|    1 | Dhoni   |
|    2 | Sehawag |
|    3 | Sachin  |
|    4 | Dravid  |
|    5 | Kohli   |
+------+---------+
5 rows in set (0.01 sec)

MariaDB [world]> SELECT * FROM Runs;
+------+------+
| pid  | Runs |
+------+------+
|    1 |  100 |
|    2 |   45 |
|    3 |   35 |
|    1 |   90 |
|    2 |   85 |
+------+------+
5 rows in set (0.00 sec)

Actual Query

SELECT a.Name ,b.TotalRunsScored 
FROM Players a 
JOIN 
  ( 
    SELECT SUM(Runs) TotalRunsScored, pid 
    FROM Runs 
    GROUP BY pid 
  ) b ON a.pid = b.pid 
ORDER BY b.TotalRunsScored 
DESC LIMIT 1;


+-------+-----------------+
| Name  | TotalRunsScored |
+-------+-----------------+
| Dhoni |             190 |
+-------+-----------------+
1 row in set (0.00 sec)

Answer 3

In case of two same sums, if you had table like this ...

                +------------+---------+
                |    P_ID    |  Runs   |
                +------------+---------+
                |      1     | 100     |
                |      2     | 100     |
                |      3     | 35      |
                |      1     | 90      |
                |      2     | 90      |
                +------------+---------+

You could use this query:

SELECT P_ID, Name, SUM(Runs) AS TotalRuns FROM Player
JOIN Runs USING(P_ID)
GROUP BY P_ID
HAVING TotalRuns = (
  SELECT SUM(Runs) AS Total FROM Player
  JOIN Runs USING(P_ID)
  GROUP BY P_ID
  ORDER BY Total DESC
  LIMIT 1
);

Which returns:

| p_id |    name | TotalRuns |
|------|---------|-----------|
|    1 |   Dhoni |       190 |
|    2 | Sehawag |       190 |

You can run query on SQL Fiddle. The idea behind the query is to get only the best sums of runs. Main query uses join and group by to get appropriate values for each row. The subquery in having (which enables listing of more max sums) also uses join and group by, but there is also order by for ordering by max sum and limit for the real max sum.

Answer 4

You could RUN this query:

SELECT
    p.name as PlayerName,
    SUM(r.Runs) as TotalRuns
FROM Players AS p
LEFT JOIN Runs AS r ON (r.pid = p.pid)
GROUP BY p.pid
ORDER BY TotalRuns DESC LIMIT 0,1;

You will get 1 row with the player who SUM(r.Runs) max. Otherwise you should think about add ORDER BY with the column that you want to bring first, because if you have 2 players with the same TotalRuns, optimizer will order the values as it wants. By the way you can add one more column on Runs, a run_date datetime and add an INDEX so you can save the player Runs time.