A Challenging Grouping Scenario

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

SQL Server 2005 Forums

Transact-SQL (2005)

A Challenging Grouping Scenario

Author

Topic

lueylump
Starting Member

25 Posts

Posted - 2010-10-26 : 15:23:20

I have a situation where I have an email address associated with multiple rows of data, but I only need to extract one of them (the one with the earliest creation date and time). The challenge is none of the other data associated with this email address is unique so when I use a Select distinct it takes all rows. Is there any way around this morass?

I included sample data below and a lame attempt at solving this problem.

Tbl A Col 1 Tbl A Col 2 Tbl A Col 3 Tbl B Col 1
Minnie Duck 10379056 11/26/2002 8:55 donald.duck@toon.com
Donald Duck 10424241 9/4/2007 14:03 donald.duck@toon.com
Huey Duck 10424256 9/4/2007 14:05 donald.duck@toon.com
Duey Duck 10424260 9/4/2007 14:05 donald.duck@toon.com
Luey Duck 30048439 4/20/2006 8:35 donald.duck@toon.com
Daffy Duck 10606334 9/4/2007 14:00 donald.duck@toon.com
Dead Duck 10580984 9/4/2007 14:02 donald.duck@toon.com

Select distinct (A.Create_Date), A.Group_Formal_Name, A.Group_Code, B.Email
from Per_Group_Loc A, Per_Emails B
where B.Per_Email_ID = A.Primary_Email_ID
and B.Email not like '%%elimcs.org%%'
order by B.Email

Thanks,

lueylump

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-10-26 : 15:29:25

try something like:
SELECT b.Email ,MAX(a.Create_Date)
FROM Per_Emails b
JOIN Per_Group_Loc a on b.per_email_id = a.primary_email_id
group by b.Email

lueylump
Starting Member

25 Posts

Posted - 2010-10-26 : 16:25:47

I'll give it a try. Thanks!!!

lueylump
Starting Member

25 Posts

Posted - 2010-10-26 : 16:35:12

You got me on the right track so thanks!!! The resulting code looks like this:

SELECT b.Email, MIN(a.Create_Date), MAX(c.Group_Formal_Name), MAX(c.Group_Code)
FROM Per_Emails b
JOIN Per_Group_Loc a on b.Per_Email_ID = a.Primary_Email_ID
JOIN Per_Group_Loc c on a.Group_Code = c.Group_Code
group by b.Email
order by b.Email

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources