Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 26th, 2007, 06:27 PM
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

Reply With Quote
  #2 (permalink)  
Old November 27th, 2007, 04:32 AM
Authorized User
 
Join Date: Sep 2003
Location: Delhi, Delhi, India.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to prabodh_mishra
Default

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
Reply With Quote
  #3 (permalink)  
Old November 27th, 2007, 05:43 AM
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #4 (permalink)  
Old November 27th, 2007, 07:38 AM
Authorized User
 
Join Date: Sep 2003
Location: Delhi, Delhi, India.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to prabodh_mishra
Default

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
Reply With Quote
  #5 (permalink)  
Old November 28th, 2007, 01:13 PM
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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??

Reply With Quote
  #6 (permalink)  
Old November 29th, 2007, 01:25 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old December 1st, 2007, 10:06 AM
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #8 (permalink)  
Old December 1st, 2007, 10:18 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old December 1st, 2007, 10:55 AM
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.:(

Reply With Quote
  #10 (permalink)  
Old December 17th, 2007, 11:10 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Distinct Eddywardo SQL Server 2005 1 September 2nd, 2006 07:41 AM
Select Distinct? hugh@kmcnetwork.com SQL Language 5 November 5th, 2005 09:58 AM
Distinct data arnabghosh Access 2 September 21st, 2005 10:46 AM
distinct problem hastikeyvan Classic ASP Basics 1 September 6th, 2005 02:47 PM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM



All times are GMT -4. The time now is 04:19 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.