Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/defaul...kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

Eliminating Duplicates

Have a pretty simple wuestion but the answer seems to be evading me:

Here's the DDL for the tables in question:

CREATE TABLE [dbo].[Office] (
[OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentOfficeID] [int] NOT NULL ,
[WebSiteID] [int] NOT NULL ,
[IsDisplayOnWeb] [bit] NOT NULL ,
[IsDisplayOnAdmin] [bit] NOT NULL ,
[OfficeStatus] [char] (1) NOT NULL ,
[DisplayORD] [smallint] NOT NULL ,
[OfficeTYPE] [varchar] (10) NOT NULL ,
[OfficeNM] [varchar] (50) NOT NULL ,
[OfficeDisplayNM] [varchar] (50) NOT NULL ,
[OfficeADDR1] [varchar] (50) NOT NULL ,
[OfficeADDR2] [varchar] (50) NOT NULL ,
[OfficeCityNM] [varchar] (50) NOT NULL ,
[OfficeStateCD] [char] (2) NOT NULL ,
[OfficePostalCD] [varchar] (15) NOT NULL ,
[OfficeIMG] [varchar] (100) NOT NULL ,
[OfficeIMGPath] [varchar] (100) NOT NULL ,
[RegionID] [int] NOT NULL ,
[OfficeTourURL] [varchar] (255) NULL ,
[GeoAreaID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[OfficeBrandedURL] [varchar] (255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeManagement] (
[OfficeID] [int] NOT NULL ,
[PersonnelID] [int] NOT NULL ,
[JobTitleID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[SeqNBR] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeMls] (
[OfficeID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[OfficeMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Personnel] (
[PersonnelID] [int] IDENTITY (1, 1) NOT NULL ,
[PersonnelDisplayName] [varchar] (100) NOT NULL ,
[FirstNM] [varchar] (50) NOT NULL ,
[PreferredFirstNM] [varchar] (50) NOT NULL ,
[MiddleNM] [varchar] (50) NOT NULL ,
[LastNM] [varchar] (50) NOT NULL ,
[PersonalTaxID] [varchar] (9) NOT NULL ,
[HireDT] [datetime] NOT NULL ,
[TermDT] [datetime] NOT NULL ,
[HomePhoneNBR] [varchar] (15) NULL ,
[HomeADDR1] [varchar] (50) NOT NULL ,
[HomeADDR2] [varchar] (50) NOT NULL ,
[HomeCityNM] [varchar] (50) NOT NULL ,
[HomeStateCD] [char] (2) NOT NULL ,
[HomePostalCD] [varchar] (15) NOT NULL ,
[PersonnelLangCSV] [varchar] (500) NOT NULL ,
[PersonnelSlogan] [varchar] (500) NOT NULL ,
[BGColor] [varchar] (50) NOT NULL ,
[IsEAgent] [bit] NOT NULL ,
[IsArchAgent] [bit] NOT NULL ,
[IsOptOut] [bit] NOT NULL ,
[IsDispOnlyPrefFirstNM] [bit] NOT NULL ,
[IsHideMyListingLink] [bit] NOT NULL ,
[IsPreviewsSpecialist] [bit] NOT NULL ,
[AudioFileNM] [varchar] (100) NULL ,
[iProviderID] [int] NOT NULL ,
[DRENumber] [varchar] (10) NOT NULL ,
[AgentBrandedURL] [varchar] (255) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[IsDisplayAwards] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PersonnelMLS] (
[PersonnelID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[AgentMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Office] ADD
CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[OfficeProfile] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile]
GO

ALTER TABLE [dbo].[OfficeManagement] ADD
CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY
(
[JobTitleID]
) REFERENCES [dbo].[LookupJobTitle] (
[JobTitleID]
),
CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) ON DELETE CASCADE
GO

alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office]
GO

ALTER TABLE [dbo].[OfficeMls] ADD
CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office]
GO

ALTER TABLE [dbo].[PersonnelMLS] ADD
CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) NOT FOR REPLICATION
GO

alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel]
GO

Here's the query I'm having trouble with:

SELECT distinct Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID AS OfficeBoard,
PersonnelMLS.SourceID AS AgentBoard
FROM Personnel INNER JOIN
OfficeManagement ON
Personnel.PersonnelID = OfficeManagement.PersonnelID
INNER JOIN
Office ON OfficeManagement.OfficeID = Office.OfficeID
INNER JOIN
OfficeMls ON Office.OfficeID = OfficeMls.OfficeID
INNER JOIN
PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID
where officemls.sourceid <> personnelmls.sourceid
and office.officenm not like ('%admin%')
group by PersonnelMLS.SourceID,
Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID
order by office.officenm

What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:

PersonnelID FirstNM LastNM OfficeNM OfficeID OfficeBoard AgentBoard
---- ---------------- ---------------- ---------------- ---- ---- ----
18205 Margaret Peggy Quattro Aventura North 650 906 908
18205 Margaret Peggy Quattro Aventura North 650 918 908
15503 Susan Jordan Blackburn Point 889 920 909
15503 Susan Jordan Blackburn Point 889 921 909
15503 Susan Jordan Blackburn Point 889 921 920
15279 Sandra Humphrey Boca Beach North 890 917 906
15279 Sandra Humphrey Boca Beach North 890 906 917
15279 Sandra Humphrey Boca Beaches 626 917 906
15279 Sandra Humphrey Boca Beaches 626 906 917
13532 Michael Demcho Boca Downtown 735 906 917
14133 Maria Ford Boca Downtown 735 906 917
19126 Michael Silverman Boca Glades Road 736 917 906
18920 Beth Schwartz Boca Glades Road 736 906 917

If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.

I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.

Any help would be greatly appreciated.

Thanks in advance!Could you edit the post and wrap [ code] [ /code ] tags around it?

Just reomve the spaces in the tags|||You're right...that is a LOT better!! Thanks for the tip...|||Do the same with the data...

Why are you doing a GROUP BY? To get DISTINCT data?

EDIT: And can you post the DDL for the tables|||...and I guess I really don't need the GROUP BY...|||As far as i can see .. the rows are not exactly duplicates ... OfficeBoard id seems to be differing ... a joi n problem .. maybe ??|||didnt read the post completely .. my bad !!!

Didnt get what exactly you were trying to accomplish ...

How do you decide that Sandra Humphries is outta office 626 ?|||Office 626 is the office she's associated with - it's set up in the main table. Been working on the join and can't seem to work out the bugs on this one...|||you could try replacing
"where officemls.sourceid <> personnelmls.sourceid"
with
"where personnelmls.sourceid not in
(select sourceid from officemls omls join office o on omls.officeid = o.officeid
where officemanagement.officeid = omls.officeid)"
This will give you duplicates because of the "distinct" but only the wrong personnelmls.sourceid should be selected.

Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.
Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default...b;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

Eliminating Duplicates

I have a table with a field called PID. This field can have duplicate
values in it. I have 2 other fields called CAU and SUF. I may have 3 rows
with the same PID and same CAU, but the SUF field when concatenated with the
2 aforementioned fields make the row unique.
I want to know how to select 1 of the 3 rows but not the other 2. In
otherwords I need 1 row for each SUF. Hopes this makes sense and I would
appreciate any help.Try the following query. Work from the inner select out.
CREATE TABLE OneSUF
(
PID INT NOT NULL,
CAU INT NOT NULL,
SUF INT NOT NULL,
Description VARCHAR(255) NOT NULL
)
INSERT INTO OneSUF VALUES (1, 2, 3, '1, 2, 3')
INSERT INTO OneSUF VALUES (1, 2, 4, '1, 2, 4')
INSERT INTO OneSUF VALUES (1, 2, 5, '1, 2, 5')
INSERT INTO OneSUF VALUES (1, 3, 1, '1, 3, 1')
INSERT INTO OneSUF VALUES (1, 3, 2, '1, 3, 2')
INSERT INTO OneSUF VALUES (1, 3, 3, '1, 3, 3')
INSERT INTO OneSUF VALUES (1, 4, 3, '1, 4, 3')
INSERT INTO OneSUF VALUES (1, 4, 4, '1, 4, 4')
INSERT INTO OneSUF VALUES (1, 4, 5, '1, 4, 5')
INSERT INTO OneSUF VALUES (1, 4, 6, '1, 4, 6')
SELECT SUF3.SUF, SUF3.CAU, SUF3.PID, SUF3.Description
FROM OneSUF AS SUF3 INNER JOIN
(
SELECT SUF1.SUF, SUF1.CAU, MIN(SUF1.PID) AS PID FROM OneSUF AS SUF1 INNER
JOIN
(SELECT SUF, MIN(CAU) AS CAU FROM OneSUF GROUP BY SUF) AS SUF2
ON SUF1.SUF = SUF2.SUF AND SUF1.CAU = SUF2.CAU
GROUP BY SUF1.SUF, SUF1.CAU) AS SUF4
ON SUF3.SUF = SUF4.SUF AND SUF3.CAU = SUF4.CAU AND SUF3.PID = SUF4.PID
ORDER BY 1,2,3
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:%23OzmAYOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> I have a table with a field called PID. This field can have duplicate
> values in it. I have 2 other fields called CAU and SUF. I may have 3
rows
> with the same PID and same CAU, but the SUF field when concatenated with
the
> 2 aforementioned fields make the row unique.
> I want to know how to select 1 of the 3 rows but not the other 2. In
> otherwords I need 1 row for each SUF. Hopes this makes sense and I would
> appreciate any help.
>

Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

eliminating (inverse) duplicates in result

Am I going about this the right way? I want to find pairs of entities
in a table that have some relationship (such as a field being the
same), so I

select t1.id, t2.id from sametable t1 join sametable t2 on
t1.id<>t2.id
where t1.fieldx=t2.fieldx ...

The trouble is, this returns each pair twice, e.g.

B C
C B
M N
N M

Is there a way to do this kind of thing and only get each pair once?

KerryTry this:

SELECT t1.id, t2.id
FROM sametable t1
JOIN sametable t2
ON t1.id < t2.id
WHERE t1.fieldx=t2.fieldx ...

(a subtle difference in the ON clause)

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks for the tip. Very elegant. Makes the join half as big too!

Thanks,

Kerry

Eliminate duplicates from only one column

--
Randycan you expand your query...'
or try this...
select distinct(one_column) from table_with_one_column|||GROUP BY clause
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:A063AE9C-6B3A-4FB8-B6D4-2BEFC7A5FF41@.microsoft.com...
> --
> Randy

Eliminate duplicates from datasource

Hi All,

I have a SP, which i run inside a for loop.

I am running the SP for all the products in a listbox.

So for each product i am having the feature extracted through the SP

But some features are the same for 2, 3 products.

So in the datatable, i am getting the featrues repeated.

IS there any way to eliminate the duplicates from datatable, from server side?

Hope i am not confusing.

Eg: product1 -- test1, test2, test3

product2 -- test2, test4

so the datatable has -- test1, test2, test3, test2, test4

-- i have to eliminate one test2 from this.

Any ideas?

Thanks

Can you just use a SELECT DISTINCT in your stored procedure or whatever you generate your datatable from?

Jeff