MYSQL Limit 1 Record per in Joined Table-CodePudding

Attempting to join two tables on user_id. The users table has unique id for each user. The user_codes table can have multiple rows with the same user_id. I only want to return 1 row from the joined user_codes table, where code_count is the largest.

users Table

| id       | email           |
| -------- | --------------- |
| 1        | [email protected] |
| 2        | [email protected] |
| 3        | [email protected] |

user_code TABLE

| user_id  | invite_code | count |
| -------- | ----------- | ------|
| 1        | X49MCL1     | 40    |
| 1        | K59CLT9     | 1000  |
| 2        | X5BC924     | 15    | 
| 2        | 38DF80L     | 8     |
| 3        | 641020T     | 22    |

EXPECTED RESULT

| id  |  email          | invite_code | count | 
| --- | --------------- | ----------- | ------|
| 1   | [email protected] | K59CLT9     | 1000  |
| 2   | [email protected] | X5BC924     | 15    |
| 3   | [email protected] | 641020T     | 22    |

The query result only includes a single instance of each user found in the user_codes table with the highest count.

Here is the closest query I could get, but it only returns the invite_code and count for the first user.

SELECT a.id, a.email, b.invite_code, b.count
FROM users a
LEFT JOIN user_codes b
ON b.user_id = a.id 
AND b.count = (SELECT MAX(count) FROM user_codes GROUP BY b.user_id)

The above query returns the result:

| id  |  email          | invite_code | count  | 
| --- | --------------- | ----------- | ------ |
| 1   | [email protected] | K59CLT9     | 1000   |
| 2   | [email protected] | `NULL`      | `NULL` |
| 3   | [email protected] | `NULL`      | `NULL` |

I can't seem to figure out how/why the records after the first one don't include the invite_code and the count.

Thanks for help!

CodePudding user response：

On MySQL 8 , I suggest using the RANK() window function:

WITH cte AS (
    SELECT u.id, u.email, uc.invite_code, uc.count,
           RANK() OVER (PARTITION BY u.id ORDER BY uc.count DESC) rnk
    FROM users u
    INNER JOIN user_code uc
        ON uc.user_id = u.id
)

SELECT id, email, invite_code, count
FROM cte
WHERE rnk = 1;

The RANK() function will also match multiple records per user tied for the highest count.

You might be able to salvage your current attempt by correlating the user inside the subquery to the outer query:

SELECT a.id, a.email, b.invite_code, b.count
FROM users a
LEFT JOIN user_codes b
    ON b.user_id = a.id AND
       b.count = (SELECT MAX(uc.count) FROM user_codes uc WHERE uc.user_id = a.id);