MySQL subquery select first row for each group

Question

I need to create a MySQL stored procedure it selects each User SUM of all the Points they've earned.

The query should group Game by StartTime and only select the first row of each group ordered by Points. I'm trying to ignore duplicate StartTime values for each User but still keep the first one. This should avoid cheating if the User saves the same game twice.

If the User hasn't been in any Game, it should still return NULL.

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` > _StartDate AND `Game`.`StartDate` < _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

Sample User Table

+--------+----------+
| UserId | Username |
+--------+----------+
|      1 | JaneDoe  |
|      2 | JohnDoe  |
+--------+----------+

Sample Game Table

+--------+--------+-------------------------+--------+
| 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 |
+--------+--------+-------------------------+--------+

Current Result

+--------+----------+-------+
| UserId | Username | Value |
+--------+----------+-------+
|      1 | JaneDoe  |  5367 |
|      2 | JohnDoe  |   552 |
+--------+----------+-------+

Expected Result

+--------+----------+-------+
| UserId | Username | Value |
+--------+----------+-------+
|      1 | JaneDoe  |  3737 |
|      2 | JohnDoe  |  1064 |
+--------+----------+-------+

I was able to get the expected result with the following statement by selecting the SUM from a subquery and hardcoding the UserId.

SELECT SUM(`x`.`Points`) FROM
(SELECT `Points`
    FROM `Game`
    WHERE `Game`.`UserId` = 1 AND
    `Game`.`StartDate` > STR_TO_DATE('01/09/2019', '%m/%d/%Y') AND `Game`.`StartDate` < STR_TO_DATE('02/09/2019', '%m/%d/%Y')
    GROUP BY `Game`.`StartDate`
    ORDER BY `Game`.`Points` ASC) AS `x`;

When I try to put that statement in a subquery like in the following statement, I get this error message Error Code: 1054. Unknown column 'User.UserId' in 'where clause'. I'm getting this error because the UserId isn't visible in the second subquery.

SELECT `User`.`UserId`, `User`.`Username`,
        (SELECT SUM(`x`.`Points`) FROM (SELECT `Game`.`Points`
            FROM `Game`
            WHERE `Game`.`UserId` = `User`.`UserId` AND
            `Game`.`StartDate` > STR_TO_DATE('01/09/2019', '%m/%d/%Y') AND `Game`.`StartDate` < 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;

@Strawberry This isn't the only statements that will be in the stored procedure. — Nicholas Bastarache, Jan 25 '19 at 21:57
Hm, maybe see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jan 25 '19 at 21:59
No one can help you if no one can understand you. Sample input? Sample output? https://stackoverflow.com/help/how-to-ask — Eric, Jan 25 '19 at 22:04
@Eric I've edited my question to add samples as you requested. — Nicholas Bastarache, Jan 28 '19 at 16:19
Does this mach your question...? https://stackoverflow.com/q/3800551/3688648 — Felk, Jan 28 '19 at 16:29

score 0 · Accepted Answer · answered Jan 28 '19 at 19:16

I changed the query to use LEFT JOIN on Game. I also added GROUP BY 'Game'.'UserId', 'Game'.'StartDate' and GROUP BY 'User'.'UserId'.

CREATE PROCEDURE `spGetPoints`(
    IN _StartDate DATETIME,
    IN _EndDate DATETIME,
    IN _Limit INT,
    IN _Offset INT
)
BEGIN
    SELECT `User`.`UserId`, `User`.`Username`,
        SUM(`Game`.`Points`) AS `Value`
        FROM `User`
        LEFT JOIN (SELECT *
            FROM `Game` 
            WHERE `Game`.`StartDate` > _StartDate AND `Game`.`StartDate` < _EndDate
            GROUP BY `Game`.`UserId`, `Game`.`StartDate`
            ORDER BY `Game`.`Points`
        ) AS `Game` ON `User`.`UserId` = `Game`.`UserId`
        GROUP BY `User`.`UserId`
        ORDER BY `Value` DESC, `User`.`Username` ASC
        LIMIT _Limit OFFSET _Offset;
END

This link also helped. Select first row in each GROUP BY group?

MySQL subquery select first row for each group

1 Answers1