unique email address records are required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unique email address records are required

Author

Topic

t77code
Starting Member

2 Posts

Posted - 2007-05-10 : 13:49:11

ms sql server

data:
idno Email Category
1 a@a.com c
2 b@b.com c
3 b@b.com c
4 b@b.com c

only unique email address record are requierd.
1 a@a.com c
2 b@b.com c
what could be the query for such requirements.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-10 : 14:00:46

Well, you don't give an awful lot of explanation, so I'm going to say it depends...
If you just need a row with each distinct email address then:

SELECT   MIN(mt.idno) AS idno, mt.Email, MIN(mt.Category) AS Category
FROM     dbo.MyTable AS mt
GROUP BY mt.Email

However, this could return non-existent groupings if you have different categories. if you want to return the earliest row per email (assuming this is the lowest idno):


SELECT   mt.idno, 
         mt.Email, 
         mt.Category
FROM     dbo.MyTable AS mt
JOIN  (  SELECT   MIN(mt.idno) AS idno
         FROM     dbo.MyTable AS mt
         GROUP BY mt.Email
       ) AS earliest
ON       mt.idno = earliest.idno

Mark

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-10 : 23:39:02

If u r concern about only the email ids then just distinct is suffice,,like

Select distinct <email col> from <your table>

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-11 : 01:50:58

First go through www.w3schools.com website, then gain minimum knowledge.after that you come and post here

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 06:49:18

or Search for "Learn SQL" here

Madhivanan

Failing to plan is Planning to fail

t77code
Starting Member

2 Posts

Posted - 2007-05-13 : 02:41:49

Great, Thank you - Mark.
Ya, I really didnt explain in detail the question, but you got it.

/*other guys still unable to understand the question*/

quote:
Originally posted by mwjdavidson

Well, you don't give an awful lot of explanation, so I'm going to say it depends...
If you just need a row with each distinct email address then:
SELECT MIN(mt.idno) AS idno, mt.Email, MIN(mt.Category) AS Category
FROM dbo.MyTable AS mt
GROUP BY mt.Email

However, this could return non-existent groupings if you have different categories. if you want to return the earliest row per email (assuming this is the lowest idno):

SELECT mt.idno,
mt.Email,
mt.Category
FROM dbo.MyTable AS mt
JOIN ( SELECT MIN(mt.idno) AS idno
FROM dbo.MyTable AS mt
GROUP BY mt.Email
) AS earliest
ON mt.idno = earliest.idno

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources