title: MySQL subquery select first row for each group
body: <p>I need to create a MySQL stored procedure it selects each <code>User</code> <code>SUM</code> of all the <code>Points</code> they've earned.</p>
  
  <p>The query should group <code>Game</code> by <code>StartTime</code> and only select the first row of each group ordered by <code>Points</code>. I'm trying to ignore duplicate <code>StartTime</code> values for each <code>User</code> but still keep the first one. This should avoid cheating if the <code>User</code> saves the same game twice.</p>
  
  <p>If the <code>User</code> hasn't been in any <code>Game</code>, it should still return <code>NULL</code>.</p>
  
  <pre><code>CREATE PROCEDURE `spGetPoints`(
      IN _StartDate DATETIME,
      IN _EndDate DATETIME,
      IN _Limit INT,
      IN _Offset INT
  )
  BEGIN
      SELECT `User`.`UserId`, `User`.`Username`,
          (SELECT SUM(`Game`.`Points`)
              FROM `Game`
              WHERE `Game`.`UserId` = `User`.`UserId` AND
              `Game`.`StartDate` &gt; _StartDate AND `Game`.`StartDate` &lt; _EndDate
              GROUP BY `Game`.`StartDate`
              ORDER BY `Game`.`Points` DESC
              LIMIT 1
          ) AS `Value`
      FROM `User`
      ORDER BY `Value` DESC, `User`.`Username` ASC
      LIMIT _Limit OFFSET _Offset;
  END
  </code></pre>
  
  <p>Sample User Table</p>
  
  <pre><code>+--------+----------+
  | UserId | Username |
  +--------+----------+
  |      1 | JaneDoe  |
  |      2 | JohnDoe  |
  +--------+----------+
  </code></pre>
  
  <p>Sample Game Table</p>
  
  <pre><code>+--------+--------+-------------------------+--------+
  | GameId | UserId |        StartDate        | Points |
  +--------+--------+-------------------------+--------+
  |      1 |      1 | 2019-01-09  12:43:00 AM |   1789 |
  |      2 |      1 | 2019-01-09  11:35:00 AM |   1048 |
  |      3 |      1 | 2019-01-09  9:22:00 AM  |    900 |
  |      4 |      1 | 2019-01-09  12:43:00 AM |   1789 |
  |      5 |      1 | 2019-01-09  11:35:00 AM |   1048 |
  |      6 |      1 | 2019-01-09  9:22:00 AM  |    900 |
  |      7 |      1 | 2019-01-09  12:43:00 AM |   1789 |
  |      8 |      1 | 2019-01-09  11:35:00 AM |   1048 |
  |      9 |      2 | 2019-01-17  12:05:00 AM |    552 |
  |     10 |      2 | 2019-01-24  12:08:00 AM |    512 |
  |     11 |      2 | 2019-01-27  5:13:00 PM  |      0 |
  +--------+--------+-------------------------+--------+
  </code></pre>
  
  <p>Current Result</p>
  
  <pre><code>+--------+----------+-------+
  | UserId | Username | Value |
  +--------+----------+-------+
  |      1 | JaneDoe  |  5367 |
  |      2 | JohnDoe  |   552 |
  +--------+----------+-------+
  </code></pre>
  
  <p>Expected Result</p>
  
  <pre><code>+--------+----------+-------+
  | UserId | Username | Value |
  +--------+----------+-------+
  |      1 | JaneDoe  |  3737 |
  |      2 | JohnDoe  |  1064 |
  +--------+----------+-------+
  </code></pre>
  
  <p>I was able to get the expected result with the following statement by selecting the <code>SUM</code> from a subquery and hardcoding the <code>UserId</code>.</p>
  
  <pre><code>SELECT SUM(`x`.`Points`) FROM
  (SELECT `Points`
      FROM `Game`
      WHERE `Game`.`UserId` = 1 AND
      `Game`.`StartDate` &gt; STR_TO_DATE('01/09/2019', '%m/%d/%Y') AND `Game`.`StartDate` &lt; STR_TO_DATE('02/09/2019', '%m/%d/%Y')
      GROUP BY `Game`.`StartDate`
      ORDER BY `Game`.`Points` ASC) AS `x`;
  </code></pre>
  
  <p>When I try to put that statement in a subquery like in the following statement, I get this error message <code>Error Code: 1054. Unknown column 'User.UserId' in 'where clause'</code>. I'm getting this error because the <code>UserId</code> isn't visible in the second subquery.</p>
  
  <pre><code>SELECT `User`.`UserId`, `User`.`Username`,
          (SELECT SUM(`x`.`Points`) FROM (SELECT `Game`.`Points`
              FROM `Game`
              WHERE `Game`.`UserId` = `User`.`UserId` AND
              `Game`.`StartDate` &gt; STR_TO_DATE('01/09/2019', '%m/%d/%Y') AND `Game`.`StartDate` &lt; STR_TO_DATE('02/09/2019', '%m/%d/%Y')
              GROUP BY `Game`.`StartDate`
              ORDER BY `Game`.`Points` DESC) AS `x`
          ) AS `Value`
      FROM `User`
      ORDER BY `Value` DESC, `User`.`Username` ASC;
  </code></pre>