Removing distinct rows 2

[courtney79]

Hi,

I am having trouble removing distinct records from the follwowing data:

Supporter email BounceBacks
1 bob@top.com 0
1 bob2@top.com 2
2 sam@fop.com 1
3 jane@lop.com 0
4 dave@pol.com 0

What I am hoping to get is get every supporters email but if they have more than one email I want the one with least bounce backs e.g.

Supporter email BounceBacks
1 bob@top.com 0
2 sam@fop.com 1
3 jane@lop.com 0
4 dave@pol.com 0

The SQL I am using at the moment is the following:

SELECT distinct supporter, email, MIN(ISNULL(BounceBacks,0)) as BounceBacks
FROM emailtable
GROUP BY supporter, email

but that still gives me all of them which i guess is because it is looking at distinct combinations as opposed to only supporters!

Any help would be much appreciated.

Cheers,
Tom

 

[vongrunt]

select A.* from blah A
inner join 
(	select Supporter, min(BounceBacks) as minBB
	from blah
	group by Supporter
) B
on A.Supporter = B.Supporter and A.BounceBacks = B.minBB

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[courtney79]

Thanks for the reply it worked fine except I now have another problem. If I have a supporter who has 2 different email addresses and they both have 0 bounce backs I need to only select the 1st one in the table!!

Any ideas?

Cheers

 

[vongrunt]

SQL table has no "top" or "bottom". Query has - if ORDER BY is specified.

What is "the 1st row" in this case? Row that appears first on the screen?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

Run vongrunt's code, and place your data into a temp table. From there you can than use a group by with a min or Max on a column to get the first row.

Jim

 

[courtney79]

Thanks both for your help, I ended up solving the problem by adding an ID key to the table and selecting the min ID key when there were equal number of bounce backs for the same supporter.

Cheers,
Tom