Getting Unique Records

[cricketer1]

I have a table containing multiple duplicate records of email, name, address etc. I want to pull Unique records containg all fields based on email. How can I do this within one query?
Thx
cricketer1

 

[osjohnm]

use a max function on the email field and use a group by statment

select name, address, max(email)
from table1
group by name, address

 

[r937]

what does "pull Unique records containg all fields based on email" mean?

if the table contains columns a,b,c, then what osjohnm gave you is

select a,b,max(c) ... group by a,b

and that's subtly different than

select a,b,c ... group by a,b,c
having count(*)=1

can you give examples of the duplicates? and which of them you want?

rudy

http://rudy.ca/

 

[cricketer1]

Good but doesn't really solve my problem.

This would be good if the data in the GROUP BY fields was exactly same in duplicate records. But the only similar data between two duplicate records is email. Other fileds could have some different values but email would remain the same.

Any other suggestions?

 

[cricketer1]

Ok, I am picking following fields: name, address, city,state, zip and email. These fields can have two different postal addresses for the same email address. e.g. home address and workaddress both having same email addresses. I need only one of those two records (I say 2 but can be more), doesn't matter which one.

Does this help?