Disctinct Query Help

Can anyone tell me how to write this query correctly... Right now it is returning all of the results instead of just the results with unique emails...

SELECT ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAdded
FROM Results
WHERE email IN (SELECT DISTINCT email FROM Results)

Please help.

Your query is doing exactly what it was written to do. Post some sample data from the table and the expected output so someone here can help fix your query.

|||

This table has entries we represent contest submissions. The form wasn't built very well and it allowed users to submit results numerous times. I want to write a query that will filter the data based on email address and phone numbers. So any records that have the same email address should be deleted. Same goes for phone number.

Should I be creating a temporary table or something?

|||

I am going toguess that you want to get a resultset where the COUNT(*) of each email is 1. How this would look:

SELECT r.ID, r.firstname, r.address1, r.lastname, r.address2, r.city, r.province, r.postalCode, r.phoneNumber, r.email, r.referral, r.cpauseInfo, r.yukonInfo, r.dateAddedFROM Results rWHEREEXISTS (select 1from Results rxwhere rx.email = r.emailgroup by rx.emailhavingcount(*) = 1 )

|||

sheldonj:

This table has entries we represent contest submissions. The form wasn't built very well and it allowed users to submit results numerous times. I want to write a query that will filter the data based on email address and phone numbers. So any records that have the same email address should be deleted. Same goes for phone number.

Should I be creating a temporary table or something?

You don't need a temp table. You are attempting to clean duplicated data. Let me refer you to an article I wrote several years agohere. Here is a sample that could remove duplicates by email address.

/* 1. Query heading */DELETEFROM/* 2. Table with duplicates */ ResultsWHEREEXISTS (SELECT 1FROM/* 3. Table with duplicates, with an alias */ Results bWHERE/* 4. Join each field with *itself*. These are fields that could be Primary Keys */ b. = Results.GROUP BY/* 5. I must GROUP BY these fields because of the HAVING clause and because these are the possible PK */ b.HAVING/* 6. This is the determining factor. We can control our output from here. In this case, we want to pick records where dateAdded is greater than the MIN dateAdded */ Results.[dateAdded] >MIN(b.[dateAdded]) )

|||

Thank you David that was exactly what I was looking for

|||

I think that simplest way to do this is:

SELECT ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAdded
FROM Results
WHERE ID IN (SELECT max(ID)
FROM Results
group by email)

it will return you only single email entries from you table.
If you would like to keep only single record with unique email in your table run

DELETE FROM Results
WHERE not ID IN (SELECT max(ID)
FROM Results
group by email)
Good luck