MySQL - Unique email, with user that has most currency

Question

Say I have a table with data like this:

+------------------------------+------------+---------+
| email                        | uname      | credits |
+------------------------------+------------+---------+
| 824@hotmail.com              | barbra     |       6 |
| 123@gmail.com                | smith      |      25 |
| 123@gmail.com                | smithy     |      30 |
| abc@hotmail.com              | another    |      25 |
| def@comcast.net              | rob        |       8 |
| abc@hotmail.com              | ben        |      62 |
| ijk@yahoo.com                | jeb        |       2 |
| ijk@yahoo.com                | joe        |       5 |
+------------------------------+------------+---------+

So there is duplicate emails, and you want to send a newsletter out. But you don't want to send multiple of the email to the same email address, and you want to have their username in the email. You probably need to address the user in the email by the username which has more credits, as that account is more likely to be active. So you want a query that'll return data like this:

+------------------------------+------------+---------+
| email                        | uname      | credits |
+------------------------------+------------+---------+
| 824@hotmail.com              | barbra     |       6 |
| 123@gmail.com                | smithy     |      30 |
| def@comcast.net              | rob        |       8 |
| abc@hotmail.com              | ben        |      62 |
| ijk@yahii.com                | joe        |       5 |
+------------------------------+------------+---------+

So it picks the username that has more credits on their account.

How can I write a query to do this? The only examples I've seen are ones that don't care which username it picks, so you could get the user with less credits.

How exactly do you intend to determine that smith and smithy are the same user? — Pierre Arlaud, Nov 29 '13 at 15:38
@Bubby4j I think you still have that same typo for the user accounts "jeb" and "joe". — cyroxx, Nov 29 '13 at 15:44

score 2 · Answer 1 · edited May 23 '17 at 10:25

2

~~This should do it:~~

~~SELECT email,uname,MAX(credits) "credits" FROM MY_TABLE GROUP BY email;~~

EDIT:

This question has some useful information. Try this instead:

SELECT `mytab`.`email`, `mytab`.`uname`, `mytab`.`credits`
FROM `my_table` `mytab`
INNER JOIN (
  SELECT `email`, MAX(`credits`) `max_cred`
  FROM `my_table`
  GROUP BY `email`
) `max` ON `mytab`.`email` = `max`.`email` AND `mytab`.`credits` = `max`.`max_cred`;

edited May 23 '17 at 10:25

Community

1
1

answered Nov 29 '13 at 15:38

r3mainer

23,981
3
51
88

That does return the correct credit value, but it'll mix and match the username with the credits. It won't select the user that has the most credits, simply the largest credit amount. – James T Nov 29 '13 at 15:42
Ah I see what you mean. – r3mainer Nov 29 '13 at 15:42

Jason Heo · Accepted Answer · 2013-11-29T15:54:51.683

2

Could you try this?

Select tab.email, tab.uname
From(
    Select email, max(credits) as credits
    From tab
    Group by  email
) x join tab on x.email = tab.email and tab.credits = x.credits

edited Nov 29 '13 at 15:54

answered Nov 29 '13 at 15:40

Jason Heo

9,956
2
36
64

1

You could probably rename "credit" into "credits" and "name" into "uname" so that the query works with the OP's schema. Here's a working version in SQLFiddle: http://sqlfiddle.com/#!2/624d68/3 – cyroxx Nov 29 '13 at 15:53
+1 I just noticed your answer is identical to the one I posted after you. (Well, almost. It definitely works.) – r3mainer Nov 29 '13 at 16:06

MySQL - Unique email, with user that has most currency

2 Answers2

Linked