eliminating duplicates

I need to elimnate duplicates the following script. I believe what is
creating the duplicates is the provcont table, there are different provcont
ids for each record. Thanks in advance.

SELECT	PROVCONT.C_FAX, 
	PROVIDER.NAME, 
	PROVIDER.PROV_PATH, 
	PROVIDER.PROV_ID, 
	PROVSTAT.STATUS, 
	PSTATUS.DESCRIPT, 
                PROVCONT.CONTAC_ID

FROM    PROVCONT 

	INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID 
	INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID 
	INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS

WHERE     (PROVIDER.PROV_PATH = 2) AND (PROVSTAT.STATUS = 3) AND PROVCONT.
CONTAC_ID

-- 
Message posted via http://www.sqlmonster.com

0
Jay
6/25/2010 9:48:21 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1017 Views

Similar Articles

[PageSpeed] 25

Jay via SQLMonster.com (u7124@uwe) writes:
> I need to elimnate duplicates the following script. I believe what is 
> creating the duplicates is the provcont table, there are different
> provcont ids for each record. Thanks in advance. 
> 
> SELECT     PROVCONT.C_FAX, 
>      PROVIDER.NAME, 
>      PROVIDER.PROV_PATH, 
>      PROVIDER.PROV_ID, 
>      PROVSTAT.STATUS, 
>      PSTATUS.DESCRIPT, 
>                 PROVCONT.CONTAC_ID
> 
> FROM    PROVCONT 
> 
>      INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID 
>      INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID 
>      INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS
> 
> WHERE     (PROVIDER.PROV_PATH = 2) AND (PROVSTAT.STATUS = 3) AND PROVCONT.
> CONTAC_ID
 
You can always take the easy route out and add DISTINCT to the SELECT
clause.

But often duplicates in the output indicates that you have not thought
the whole way through of what you are looking for. Since I don't know
your tables, I can't say what might be wrong here - or whether anything
is wrong at all.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/25/2010 10:29:00 PM
Jay and Erland,
I've successfully used the following 'very short' update routine to isolate 
and then remove duplicate records.

--Used to de-dup records

--Table must have (a) Rec_ID column Identity(1,1), AND (b) DUP column 
Char(1)

--THis procedure de-dups records that contain identical Matchkey (dupcolumn)

--records within the table itself.

To implement the following you need to:

A) Determine which column has the duplicates that you need to de-dup

B) Have a unique RecordID column of type INT - use Identity(1,1)

C) Alter the table and add a DUP column of type Char(1)

D) You can then inspect the table to see that the column Dup marked with a 
'Y'

is an actual duplicate record based on your dupcolumn.

E) You can then delete all records that have DUP='Y'



UPDATE table_name SET DUP = ''Y''

FROM

table_Name

WHERE EXISTS

(SELECT NULL FROM table_name B WHERE B.dupcolumn = table_name.dupcolumn

GROUP BY B.dupcolumn

HAVING table_Name.rec_ID <MAX(B.rec_ID))


Enjoy

-Ray


"Jay via SQLMonster.com" <u7124@uwe> wrote in message 
news:aa12a587a82d4@uwe...
>I need to elimnate duplicates the following script. I believe what is
> creating the duplicates is the provcont table, there are different 
> provcont
> ids for each record. Thanks in advance.
>
> SELECT PROVCONT.C_FAX,
> PROVIDER.NAME,
> PROVIDER.PROV_PATH,
> PROVIDER.PROV_ID,
> PROVSTAT.STATUS,
> PSTATUS.DESCRIPT,
>                PROVCONT.CONTAC_ID
>
> FROM    PROVCONT
>
> INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID
> INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID
> INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS
>
> WHERE     (PROVIDER.PROV_PATH = 2) AND (PROVSTAT.STATUS = 3) AND PROVCONT.
> CONTAC_ID
>
> -- 
> Message posted via http://www.sqlmonster.com
> 


0
ray
6/26/2010 6:52:26 PM
Reply:

Similar Artilces:

Eliminate unused columns
I have a table of columns of data per week, which I convert to a table of columns of data per month. This leaves 3 or 4 blank columns between the months. Is there a simple way to consolidate the month columns into a contiguous region, eliminating the unused columns between the month columns? Or... is there a smarter way to do the conversion than sum across an fill down? One approach is to use a macro: if your empty columns are completely empty you could use this macro. Hit Alt-F11, Click Insert/Module (check the project window to make sure your workbook is the active project before...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

duplicate asset label
Hi, We are using GP9 with sp2. When creating new asset in Asset General Information window, if the asset label already exists the message 'The Asset Label is already assigned to another asset.' which is correct. But upon checking the FA00100 table, there are some assets that have duplicate Asset Label. How come GP let it pass through without errors? There were more than 39 assets that have duplicate asset label. Hope someone can help me with this. Thanks. Were any of the assets imported? "mel" wrote: > Hi, > > We are using GP9 with sp2. When creating new...

eliminate duplicates
I have a table with 7 different columns of team members on different teams. I need a count of all team members. How do I query seven different columns and eliminate dups? You've discovered yet another problem with denormalized tables. If you have 7 different columns, each containing the same basic information, you have a repeating group. If you can't go back and redesign your tables, the easiest approach would be to create a UNION query that normalizes the data so that it's in only 1 column: SELECT Member1 AS Member FROM MyTable UNION SELECT Member2 AS Member FROM MyTabl...

eliminating duplicates
I need to elimnate duplicates the following script. I believe what is creating the duplicates is the provcont table, there are different provcont ids for each record. Thanks in advance. SELECT PROVCONT.C_FAX, PROVIDER.NAME, PROVIDER.PROV_PATH, PROVIDER.PROV_ID, PROVSTAT.STATUS, PSTATUS.DESCRIPT, PROVCONT.CONTAC_ID FROM PROVCONT INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS WHERE (PROVIDER.PROV_PATH = 2) AND ...

Duplicate Draft Email Created
Hi y'all My client sends an e-mail through the SFO client. The activity is successfully created in CRM, but the email appears in the Draft folder twice. I believe that the email has been sent although it doesn't appear in the sent items. Anyone had this problem before? I checked for a duplicate email address in CRM but couldn't find one. Thanx Morag hi, We've got the same problem and on of our sales sent it twice thinking he forgot to send it. The first message is copied when your writing the message, as usual with Outlook. The second one is created when you send t...

eliminate message from append query to eliminate duplicate record.
Create a new query in design view. Add the two tables for which you wish to compare. When you see the two tables in the design view of the query click on one of your 2 join fields and drag to the corresponding join field in the other table (a line should appear) then double click on fields to add them to the query. For your comparison field Your going to have to explain how you want to compare the two fields. What type of field is in table B, and what kind of comparison do you want to do? You could start with something simple like entering "ComparisonField:Iif(tableA.field1=tableB.field2,...

Duplicating Text #2
When I am working on my newsletters I want to duplicate some text that I type in one text frame to another text frame on another page (i.e. date of newsletter, volume # etc.) How do I do this withiut re-typing it every time?? Please help. Well, you seem to know how to do that in a newsgroup. :-) Create a master page with the data you want repeated. -- JoAnn Paules MVP Microsoft [Publisher] "Blair" <kbuxon@hotmail.com> wrote in message news:2263c01c45d55$bbae6b70$a301280a@phx.gbl... > When I am working on my newsletters I want to duplicate > some text that I t...

Duplicate Lines
Hello, I have a workbook that has 30 columns, and roughly a thousand rows. Each Row should be unique and is comprised of these 30 different terms. I am trying to think of a way to add a 31st column to the end of the data which checks its respective row against all other rows to check that none are duplicated and will return FALSE if any match. Any ideas? Many Thanks Ed --- Message posted from http://www.ExcelForum.com/ The following array formula will coompare A3:AD3 to A1:AD1 and A2:AD2 and will return 1 or 2 if row 3 matches row 1 or 2. If row 3 is unique, then the formula will ...

Duplicating a Table
I have a simple table filled with data. I would like to duplicate (# rows & columns, and the width of columns) the table w/o the data. Word 2007. How is that done? -- Wayne Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39� 15' 7" N, 121� 2' 32" W, 2700 feet Web Page: <www.speckledwithstars.net/> Copy/paste the table. Make sure it is not set to automatically resize to fit contents. Then delete the contents. This s...

Selecting unique record from duplicate
Hi, please help I've just run a 'find duplicates' query on a table. Now I'm trying to select a single unique record from each duplicate. One of my fields is called 'OBSERVER' - this contains either a persons name OR 'unknown' if the persons name is not known. If one of the duplicate records contains a persons name and the other duplicate is 'unknown', how do I select the record with the persons name? If both duplicates contain 'unknown' or both contain a name, i don't mind which record gets selected. See example below... Uni...

eliminating hyper links
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel How do I get word not to link when I make reference (footnote in a book) preceded by http: etc? Daniel_Barnett@officeformac.com wrote: > Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel > How do I get word not to link when I make reference (footnote in a book) > preceded by http: etc? From Word > Preferences choose the AutoCorrect button. Click the AutoFormat as you Type tab. Uncheck Internet and network paths and hyperlinks Click OK There's more about this on the fo...

Duplicate Mailboxes
Sorry for a simple question, but I really am puzzled. I have installed Outlook 2003 at home and I am really surprised by the following situation: On the left hand side of the screen headed 'Mail', I have 'Favorite Folders' including 'Inbox' etc. Underneath that I have 'All Mail Folders', subsection 'Personal Folders', also including 'Inbox' etc. Now when an email comes in, it goes to both Inboxes and whatever I do in the Favorite Folders (deleting etc.) is duplicated by the computer in the corresponding Personal Folder. I think this is pec...

CRM 3.0 need workflow example for duplicate detection
If you have a sample of how to write a workflow to detect duplicates based on If email address matches, deactivate one of the multiple records could you please share it with me? Greatly appreciated! -- Thank you, scrowley(AT)littleonline.com I would recommend that you look into pre-callout for this task instead of workflow. Workflow is a asyn process, have you consider which record to deactivate when a duplicate record is identifier? If it's a duplicate, why create the record in the first place. Darren Liu, Microsoft CRM MVP Crowe Horwath http://www.crowecrm.com On Jan 13, 9:11=A0...

XP Office outlook personal folders
Through some technical glitch, i lost all my personal folders months ago - all emails, contacts etc. After some fruitless weeks of revovery efforts, i simply started all over. now, the original set of personal folders, all of them, have reappeared and are fully accessible. i need to know how to merge all the email folders, contacts, etc, but since XP office does not appear to contemplate this possibility, i am unable to find hints on how to merge. Any ideas?? Thanks. ...

Duplicate Contacts?
Recently I discovered, in order to use the reminder function on the calendar, I had to be signed in to Windows Live Mail. Previously I had not been signing in when using WLM. So I went ahead and signed in, and now I am automatically signed in when I launch WLM. Since doing so however, I now have duplicates for all my contacts in my contacts folder. Is there a reason for this? I am using WLM build 14.0.8117.0416 -- Don The sign in is to Live services, not to WLM. Signing in syncs the contacts with the online contacts. Signing in does not cause contacts to be im...

Mathematical elimination
how could i use excel to eliminate certain feilds like how leagues are able to eliminate certain teams for playoff berth -- da_big_k ------------------------------------------------------------------------ da_big_k's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30315 View this thread: http://www.excelforum.com/showthread.php?threadid=499792 ...

Eliminating data repitition
Hi all. I am working on creating a worksheet that keeps track of files and folders that need to be routed. The files all have a number specific to a folder, and so it makes sense for that to be the basis for orginization. To save a lot of time trying to find folders that are jumbled together, I wanted to find a way for Excel to alert me whenever a duplicate file number is entered in a column. That way, I know when I can paper clip everything together when it comes time to file, and I don't need to find the same folder five times. I bet there is something with data verification ...

Duplicate Contacts
Some users on our network have experienced a "weird" phenomenon. Their Contacts list seems to duplicate itself. The first time it happened, I kind of looked into it, but could find nothing unusual so it dropped. Today it happened again, only to a different user. Has anyone seen behavior like this; and more importantly, did you determine the cause and/or fix. Any thoughts, suggestions, ... would be greatly appreciated. We are running SBS 2K3. Exchange is at SP2. Outlook 2K3 is at sp1 I believe Thanks, Bob Showalter, Packer International Where do you notice the duplicatio...

Duplicate C & D Drives
While deleting a program, I did a Windows search to be sure that I had removed all the files on the computer. What I found listed in the search results were duplicates of the C & D Drives. The program I had deleted was still listed on the duplicate D Drive but there was no option for deleting the files. In fact when I tried to reinstall the program, it told me it was already installed to d:\progra~1 instead of D:\Program Files. I tried going into MS-DOS to delete the files there but access was denied for all of them. I went ahead and reinstalled the program but it still shows that...

duplicate emails #5
I have three email accounts (all same host) and use rules wizard to separate incoming mail to three sub-folders in Inbox. For one of the accounts only, the mail arrives properly in its sub-folder, but a second copy of the emails arrives in the regular Inbox folder. For the other two accounts, the emails arrive only in their appropriate sub-folders. Any ideas? >-----Original Message----- >I have three email accounts (all same host) and use rules >wizard to separate incoming mail to three sub-folders in >Inbox. For one of the accounts only, the mail arrives >properl...

Duplicate Calendars
As a Outlook 2007 user, I now find myself with: ALL MAIL ITEMS- xPersonal Folders, Archive Folders and Personal Folders. MY CALENDARS- xCalendar, Calendar, Calendar in Archives Folders, Calendar in Personal Folders, and Calendar in Archives Folders. MY CONTACTS; Contacts and Contacts and Contacts in Personal Folders. MY TASKS; Tasks, Tasks in Archive Folders and Tasks in Personal folders (1)..this gives me duplicate tasks. How do I clean these up? Will simply deleting the the 2nd Personal Folder solve all of the above duplications? you can try closing the second personal fo...

Accidental Duplicate Messages
Is it possible to recall messages (like the accidental duplicate one above) once posted in the community or are you stuck with the embarrassment of doing something dumb? Ted Smith Sadly you're pretty much stuck. Most servers don't respond to recall or delete requests, those that slurp from the newsgroup and port to Web pages definitely won't, nor will archive sites. "Ted Smith" <me@mynet.net> wrote in message news:#24rQJxkKHA.4772@TK2MSFTNGP04.phx.gbl... > Is it possible to recall messages (like the accidental duplicate one > above) on...

Eliminating MyWebSearch?
My kids downloaded something called MyWebSearch which has taken over IE and Outlook (it adds a toolbar to the menus). I've been trying to eliminate it from MY account, it's mostly gone, but I can find no way to remove it from the "New Message" dialog box. Any help will be appreciated. Thanks, Mike See if it doesn't show up in Tools-> Options-> tab Other-> button Advanced Options...-> button Add-In Manager/COM Add-ins... Additionally you can reset the Toolbar by renaming outcmd.dat to outcmd.old -- Robert Sparnaaij [MVP-Outlook] www.howto-outlo...

Eliminate hyperlinks
I have a rather large spreadsheet that I copy/pasted off the net. It's test data on the students in my school. When I copied the info into Excel, their names, headings, and data all come with hyperlinks that lead me to this testing website. My question is this: Is it possible to eliminate all these hyperlinks at one time and if so, how? Thanks. I'm using Excel 2000 but can switch it to Excel XP (2003?) Dale One way, select an empty cell, copy it (ctrl + c), select the range with hyperlinks (obviously not with the mouse) then do edit>paste special and select add -- Regard...