how to select rows based on distinct values of A COLUMN only
I need to query a table in order to return rows, but I am not able to query the table correctly. Here is my table view:
Id MailId EmailAddress Name
1 1 [email protected] Mr. A
2 1 [email protected] Mr. B
3 1 [email protected] Mr. C
4 1 [email protected] Mr. D
5 1 [email protected] Mr. A
6 2 [email protected] Mr. E
7 2 [email protected] Mr. A
8 3 [email protected] Mr. F
9 4 [email protected] Mr. D
10 5 [email protected] Mr. F
11 6 [email protected] Mr. D
The result set should return:
Id MailId EmailAddress Name
1 1 [email protected] Mr. A
2 1 [email protected] Mr. B
3 1 [email protected] Mr. C
4 1 [email protected] Mr. D
6 2 [email protected] Mr. E
8 3 [email protected] Mr. F
In other words: first, I want to select distinct e-mail addresses, and then return rows containing distinct e-mail addresses.
Note: Just using the "Distinct" keyword will not work here, as it will select distinct rows. My requirement is to select distinct email addresses, and then to select rows containing those addresses.
Edit: I cannot use the "Group By" keyword either, because for this I will also have to Group By with Id (which is the PK) and doing this will return two rows with the same EmailAddress values but with different Ids.
Answer
Looking at your output maybe the following query can work, give it a try:
SELECT * FROM tablename
WHERE id IN
(SELECT MIN(id) FROM tablename GROUP BY EmailAddress)
This will select only one row for each distinct email address, the row with the minimum id which is what your result seems to portray