Thread: Using Distinct
View Single Post
 
Old November 26th, 2007, 06:27 PM
Smeghead Smeghead is offline
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Distinct

I am having problems with using distinct.

I am trying to bring back a result from a database that contains contact details of several contacts at the same locations both on the same table.

I only want to bring back the DISTINCT phone number, but also display the one of the contact details using that phone number.

When I do a SELECT DISTINCT TEL, FIRSTNAME, LASTNAME, ADDRESS1, TOWN COUNTY FROM [TABLENAME]

WHERE ......

GROUP BY FIRSTNAME, LASTNAME, ADDRESS1, TOWN COUNTY, TEL

It ignores the distinct on the phone number and I still get duplicate contacts. The problem still occurs if I do a count.

The problem I've found is that the group by also requires the field names in the select which then negates the distinct.

There are no unique id's on the table, which means I am not using unique keys or identity columns and without changing the database table structure on an existing table I am trying to use the best of what I have. I need to produce the data into another table for an export, which mustn't contain duplicates. I don't have a problem with the export or transfering the output,just the elimination of the duplicates, without messing about with a delete.

Can this be done or is there another way using code or function that I'm not aware of.

Thanks