I want retrieve one distinct id for all duplicate user emails instead of all id where duplication occured.
I have a table of users. This table has an ID and an Email. in the table the ID is unique however the email is not unique. there are duplicate emails.
I want return an ID of each duplicate entry of email. I do not want to return all the ID of duplicate emails. If there 2 IDs from one duplicate entry I want to return 1 of them.
| ID | |
|---|---|
| 1 | [email protected] |
| ----- | ------------- |
| 2 | [email protected] |
| ----- | ------------- |
| 3 | [email protected] |
| ----- | ------------- |
| 4 | [email protected] |
| ---- | ------------ |
| 4 | [email protected] |
| ---- | ------------ |
In this case I want to return 1 or 3 for [email protected] not both, and 2 or 4 for [email protected]. I have tried some thing like this but this is not working.
SELECT id FROM (SELECT distinct(email), id from users where email = email);
I am therefore to look for support. does anybody have an idea on how to solve this?
NOTE:
normally it is possible to get duplicate by using the count(email) >1. However this will return all the duplicates information, and this not what I want. I only want one value for each duplicated value so 1 out of 2 possible values.
So that is why I am using Distinct on email. However I interested in ID of the distinct email, not directly in the email. This ID is what I want to use later. So that is why I did a select distinct(email), ID from users; because I want to also have the ID of the distinct email. Then select the ID of the returned select results. However the problem is that this syntactically not correct.