Using Distinct

Smeghead · November 26th, 2007, 06:27 PM

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

prabodh_mishra · November 27th, 2007, 04:32 AM

If u have a primary key (PKEY) or any numeric field u can use the following:

SELECT TEL, FIRSTNAME, LASTNAME, ADDRESS1, TOWN COUNTY
FROM [TABLENAME]
INNER JOIN (SELECT MAX(PKEY) TempPKEY FROM [TABLENAME] group by TEL) Temp
ON [TABLENAME].PKEY = Temp.TempPKEY

Cheers,
Prabodh

Smeghead · November 27th, 2007, 05:43 AM

The problem is there is no primary key and the Tel field is a varchar. I cant change the structure of the table

Any ideas?

prabodh_mishra · November 27th, 2007, 07:38 AM

No probs. use it

SELECT TEL, FIRSTNAME, LASTNAME, ADDRESS1, TOWN COUNTY
FROM [TABLENAME]
INNER JOIN (SELECT MAX(FIRSTNAME) TempPKEY FROM [TABLENAME] group by TEL) Temp
ON [TABLENAME].FIRSTNAME = Temp.TempPKEY

Cheers,
Prabodh

Smeghead · November 28th, 2007, 01:13 PM

Sorry but the code didn't get rid of duplicates, it found them. Plus it took over an hour to run, which made my poor server groan under the strain.

Any more ideas??

Jeff Moden · November 29th, 2007, 01:25 AM

Code:

 SELECT TEL, FIRSTNAME, LASTNAME, ADDRESS1, TOWN, COUNTY FROM [TABLENAME]
   INTO #MyHead
  WHERE 1 = 0

 INSERT INTO #MyHead (TEL)
 SELECT DISTINCT Tel
   FROM [TABLENAME]

 UPDATE #MyHead
    SET FirstName = t.FirstName,
        LastName  = t.LastName,
        Address1  = t.Address1,
        Town      = t.Town,
        Country   = t.Country
   FROM #MyHead mh,
        [TABLENAME] t
  WHERE mh.Tel = t.Tel

 SELECT * FROM #MyHead

--Jeff Moden

Smeghead · December 1st, 2007, 10:06 AM

Thanks. Didn't quite have the desired effect. I think the problem is, I have only one column that's the duplicate, so its trying to find a way to bring back the results, eliminating or removing the duplicate row.

Scenario: I have multiple contacts at the same address which means they all have the same phone number. I only want to return the first contact, using the same phone number. So only one contact is returned.

Example: Mr John Smith, 17 The Street, London, 123456789
Mrs Jean Smith 17 The Street, London, 123456789

I want to bring back Mr John Smith or Mrs Jean Smith but not both.

I'm really struggling trying to find the best way round this. It may not work using distinct. I'm now investigating other functions that may well have the desired effect.

Any ideas gratefully received

Jeff Moden · December 1st, 2007, 10:18 AM

Yeah, sorry... missed that...

How many phone numbers do you want to do this for in a single shot... just one or all of the phone numbers in the table?

--Jeff Moden

Smeghead · December 1st, 2007, 10:55 AM

All of the phone numbers in the table, its so a mailshot or telephone campaign can be done, so I may have several people living at the same address, but I only want to do one letter or phone call to only one member of the household.

Scenario: I may want to do a mailshot for people living in a certain postcode (zipcode) area or maybe target a certain age group

I tried using MAX or MIN with a combination of HAVING count(*) = 1

Because the row was not uniquely the same it kept seeing the row as a different one, as the contact name was different.

Doing a SELECT COUNT (DISTINCT TEL) seem to work for the count but because I then wanted to an output using all the fields, it then went a bit haywire.

SQL can be so frustrating at times.:(

Gert · December 17th, 2007, 11:10 AM

Hi.

Are you sure Jeff's code doesn't do what you want?
Since he is joining the [Tablename] with the #myhead-table on the distinctive Tel-coloumn, then only the last of the updates for a duplicate will stick.

I tried his code, and it gave only one person pr telephone-number.

(Only thing is that there is a syntax error at the beginning, it should say:
SELECT TEL, FIRSTNAME, LASTNAME, ADDRESS1, TOWN, COUNTY INTO #MyHead
FROM [TABLENAME]
WHERE 1 = 0

Or am I missing somtehing in your picture?