how to select rows based on distinct values of A COLUMN only

Question

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               a a com               Mr  A 2                 1               b b com               Mr  B 3                 1               c c com               Mr  C 4                 1               d d com               Mr  D 5                 1               a a com               Mr  A 6                 2               e e com               Mr  E 7                 2               a a com               Mr  A 8                 3               f f com               Mr  F 9                 4               d d com               Mr  D   10                5               f f com               Mr  F 11                6               d d com               Mr  D   The result set should return   Id                MailId          EmailAddress          Name 1                 1               a a com               Mr  A 2                 1               b b com               Mr  B 3                 1               c c com               Mr  C 4                 1               d d com               Mr  D 6                 2               e e com               Mr  E 8                 3               f f com               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

User · Answer

if you dont wanna use DISTINCT use GROUP BY   SELECT   FROM myTABLE GROUP BY EmailAddress

User · Answer

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

User · Answer

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

User · Answer

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   b id   hope this help

User · 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

[sql] how to select rows based on distinct values of A COLUMN only

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to distinct