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                    Mr. A
2                 1                    Mr. B
3                 1                    Mr. C
4                 1                    Mr. D
5                 1                    Mr. A

6                 2                    Mr. E
7                 2                    Mr. A
8                 3                    Mr. F
9                 4                    Mr. D

10                5                    Mr. F
11                6                    Mr. D

The result set should return:

  • Most efficent method for adding leading 0's to an int in sql
  • Using regular expression within a stored procedure
  • SQL UPDATE statement to switch two values in two rows
  • SQL c# determining type of sql server
  • SQL Server 2005 - Select top N plus “Other”
  • SQL Server - conditional aggregation with correlation
  • Id                MailId          EmailAddress          Name
    1                 1                    Mr. A
    2                 1                    Mr. B
    3                 1                    Mr. C
    4                 1                    Mr. D
    6                 2                    Mr. E
    8                 3                    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.

    5 Solutions collect form web for “how to select rows based on distinct values of A COLUMN only”

    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

    Try this – you need a CTE (Common Table Expression) that partitions (groups) your data by distinct e-mail address, and sorts each group by ID – smallest first. Then you just select the first entry for each group – that should give you what you’re looking for:

    ;WITH DistinctMails AS
        SELECT ID, MailID, EMailAddress, NAME,
            ROW_NUMBER() OVER(PARTITION BY EMailAddress ORDER BY ID) AS 'RowNum'
        FROM dbo.YourMailTable
    SELECT *
    FROM DistinctMails
    WHERE RowNum = 1

    This works on SQL Server 2005 and newer (you didn’t mention what version you’re using…)

    if you dont wanna use DISTINCT use GROUP BY

     SELECT * FROM myTABLE GROUP BY EmailAddress

    use this(assume that your table name is emails):

    select * from emails as a 
    inner join  
    (select EmailAddress, min(Id) as id from emails 
    group by EmailAddress ) as b 
    on a.EmailAddress = b.EmailAddress 
    and a.Id =

    hope this help..

    I am not sure about your DBMS. So, I created a temporary table in Redshift and from my experience, I think this query should return what you are looking for:

    select min(Id), distinct MailId, EmailAddress, Name
        from yourTableName
        group by MailId, EmailAddress, Name

    I see that I am using a GROUP BY clause but you still won’t have two rows against any particular MailId.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.