Show just one record from duplicates

[jamespeters01]

I have a main table (AllContactsInLists) and have used the following code to show me all duplicates that occur within this table.

In (SELECT [Address] FROM [AllContactsInLists] As Tmp GROUP BY [Address] HAVING Count(*)>1 )

Some of the duplicates contacts have many duplicates.
What I would like is to just show me one contact ([Address]) from each of the duplicates this codes produces.


Thanks for any advice.

 

[cheerio]

SELECT DISTINCT [Address] FROM [AllContactsInLists]

will show only one of each address

 

[substitute]

How many fields in [allcontactsinlists]? No matter, does something like this work?

select [address], first([name]), first([phone]) <etc..>
from [allcontactsinlists]
group by [address]
having count(*)>1

If you just want to randomly deduplicate [allcontactsinlists] leave out the HAVING clause - it is only there to eliminate non-duplicated entries.

 

[jamespeters01]

Cheerio,

Had already tried SELECT DISTINCT etc

Keep getting a message 'At most one record can be returned by this subquery'

 

[cheerio]

I was simply suggesting a simple SQL query that will give a list of all the addresses excluding duplicates. For that there is no need for your subquery.

If you simply want a list of records that have duplicates you could try:

SELECT * FROM [AllContactsInLists] where [Address] IN
(SELECT [Address] FROM [AllContactsInLists] As Tmp GROUP BY [Address] HAVING Count(*)>1 )

 

[substitute]

Cheerio: I think part of James' issue is that he only wants one record per address out of the many duplicates, so he needs group by [address] and first() (or last(), or min() whatever).

James: If Cheerio and myself are making any sense you shouldn't be getting that 'at most one record' error message. Looks as if you are using it in an inappropriate place. Where are you using your query? In your first post you had IN (...) which suggests you were using it as part of some bigger query. What was the bigger query? Why were you including the 'as tmp' alias?

If we know all this you may get a better answer!