0

I have a user table like below. How can I get only non duplicate records for each username? e.g. for username = AAA, I only want to get the row with id = '1' and id ='5', and for username = BBB the row with id = '2'.

id     email            username
1      test1@test.com   AAA
2      test1@test.com   BBB
3      test2@test.com   CCC
4      test1@test.com   AAA
5      test2@test.com   AAA
mnist
  • 6,571
  • 1
  • 18
  • 41
karolo22
  • 3
  • 2

1 Answers1

0

You can filter with a correlated subquery:

select t.*
from mytable t
where t.id = (
    select min(t1.id) 
    from mytable t1 
    where t1.username = t.username and t1.email = t.email
)

With an index on (username, id), this should be an efficient solution.

If you are running MySQL 8.0, another option is to use row_number():

select id, email, username
from (
    select t.*, row_number() over(partition by username, email order by id) rn
    from mytable t
) t
where rn = 1
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I want to receive all unique email values for each username. This solution will only return the line with id = '1'. For AAA username it should also return a line with id = '5', because email value is other – karolo22 May 13 '20 at 22:16
  • Since karolo22 wants id (1, 5) for AAA, wouldn't `email` also be included in the `partition by` statement? – Kurt Kline May 13 '20 at 22:17
  • @karolo22 if you are using GMB's second query, you can change `partition by username` to `partition by username, email`. This should give you what you need. – Kurt Kline May 13 '20 at 22:19
  • Maybe second query will works, but i have older version mysql - 5.7 and i can't check – karolo22 May 13 '20 at 22:31
  • @karolo22: ok. See my edited answer then. – GMB May 13 '20 at 22:40
  • @GMB Thanks. It works very well :) – karolo22 May 13 '20 at 22:49