Another Big Concern Duplicates Equal Frustration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Another Big Concern Duplicates Equal Frustration

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-26 : 16:16:24

Duplicates are killing me in a huge MS ACCESS application. I've tried everything, but no luck.

Here's my code...set so you can cut and paste...

DROP TABLE #Table1
DROP TABLE #Table2

CREATE TABLE #Table1
(SID varchar (7) NOT NULL
, Designation varchar(50) NOT NULL
, EventP varchar (5) NOT NULL
)
GO

INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PSISERP', 'Psi Serpentis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PSISERP', 'Psi Serpentis ', 'JAN02')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('IOTHORO', 'Iota Horologii ', 'MAY09')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('EPSERID', 'Epsilon Eridani', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('ZE2RETI', 'Zeta 2 Reticuli', 'AUG04')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('ZE2RETI', 'Zeta 2 Reticuli', 'SEP04')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', 'DEC07')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', 'DEC07')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PI3ORIO', 'Pi-3 Orionis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PI3ORIO', 'Pi-3 Orionis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PI3ORIO', 'Pi-3 Orionis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('KAPFORN', 'Kappa Fornacis ', 'JAN02')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('VV_CEPH', 'VV Cephei ', ' ')

I'm TRYING to count each group and eliminate duplicates that have NO EventP
But AT LEAST single entry for each,
However where there are multiple entries, I want the ones with dates only
if no dates, then a single blank one will do.

This is the list I’m trying to create:

--PSISERP Psi Serpentis JAN02
--IOTHORO Iota Horologii MAY09
--EPSERID Epsilon Eridani
--ZE2RETI Zeta 2 Reticuli AUG04
--ZE2RETI Zeta 2 Reticuli SEP04
--SIGDRAC Sigma Draconis DEC07
--SIGDRAC Sigma Draconis DEC07
--PI3ORIO Pi-3 Orionis
--KAPFORN Kappa Fornacis JAN02
--VV_CEPH VV Cephei

I tried this, but it deletes EVERYTHING that is duplicated -- NOT GOOD

DELETE from #Table1
WHERE SID In
(SELECT [SID] FROM [#Table1] As Tmp GROUP BY [SID],[Designation] HAVING Count(*)>1)
GO

SELECT * FROM #Table1
GO

And it gives me

--IOTHORO Iota Horologii MAY09
--EPSERID Epsilon Eridani
--KAPFORN Kappa Fornacis JAN02
--VV_CEPH VV Cephei

How do I get this?

--PSISERP Psi Serpentis JAN02
--IOTHORO Iota Horologii MAY09
--EPSERID Epsilon Eridani
--ZE2RETI Zeta 2 Reticuli AUG04
--ZE2RETI Zeta 2 Reticuli SEP04
--SIGDRAC Sigma Draconis DEC07
--SIGDRAC Sigma Draconis DEC07
--PI3ORIO Pi-3 Orionis
--KAPFORN Kappa Fornacis JAN02
--VV_CEPH VV Cephei

Thanks for any help you can offer!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 16:59:48

DELETE from #Table1
WHERE SID In
(SELECT [SID] FROM [#Table1] As Tmp GROUP BY [SID],[Designation] HAVING Count(*)>1)
and eventp = ''

E 12°55'05.63"
N 56°04'39.26"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-26 : 17:12:58

I also said...

I'm TRYING to count each group and eliminate duplicates that have NO EventP
But AT LEAST single entry for each,
However where there are multiple entries, I want the ones with dates only
if no dates, then a single blank one will do.

This item: --PI3ORIO Pi-3 Orionis
should have been in the list....

How do I keep from dropping ALL of the ones that have nothing in the EventP, when I need to keep at least one entry of it?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 17:47:47

[code]CREATE TABLE #Table1
(
rowID INT IDENTITY(1, 1),
, SID varchar (7) NOT NULL
, Designation varchar(50) NOT NULL
, EventP varchar (5) NOT NULL
)
GO

INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PSISERP', 'Psi Serpentis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PSISERP', 'Psi Serpentis ', 'JAN02')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('IOTHORO', 'Iota Horologii ', 'MAY09')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('EPSERID', 'Epsilon Eridani', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('ZE2RETI', 'Zeta 2 Reticuli', 'AUG04')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('ZE2RETI', 'Zeta 2 Reticuli', 'SEP04')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', 'DEC07')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('SIGDRAC', 'Sigma Draconis ', 'DEC07')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PI3ORIO', 'Pi-3 Orionis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PI3ORIO', 'Pi-3 Orionis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('PI3ORIO', 'Pi-3 Orionis ', ' ')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('KAPFORN', 'Kappa Fornacis ', 'JAN02')
INSERT INTO #Table1 (SID, Designation, EventP) VALUES('VV_CEPH', 'VV Cephei ', ' ')

-- First delete all records with empty space for EventP for those records which have both empty space and not for EventP
DELETE t1
FROM #Table1 AS t1
WHERE t1.SID IN (SELECT SID FROM #Table1 GROUP BY SID, Designation HAVING MIN(EventP) = '' AND MAX(EventP) > '')
AND t1.EventP = ''

-- Now delete all duplicate records which have empty space only for EventP
DELETE t1
FROM #Table1 AS t1
INNER JOIN (
SELECT SID,
MIN(rowID) AS rowID
FROM #Table1
GROUP BY SID,
Designation
HAVING MAX(EventP) = ''
) AS x ON x.SID = t1.SID
WHERE t1.rowID > x.rowID

SELECT *
FROM #Table1[/code]
EDIT: Added SID for aggregation

E 12°55'05.63"
N 56°04'39.26"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-27 : 10:36:14

Peso, Thanks so much for your attempt to help me, but I'm afraid you didn't test this yourself. I got an error on the first misplaced comma, once corrected, I now get "Server: Msg 207, Level 16, State 3, Line 25 Invalid column name 'SID'." That doesn't help.

Anyone else wanna take a stab at this?

Thanks again.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 11:21:59

See edit above. I don't have SQL Server installed @home at the moment.

E 12°55'05.63"
N 56°04'39.26"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-27 : 12:43:46

Thanks, Peso! It works!

Although I had hoped I could perform this in a single step, this will suffice.

Have a great day in Sverige (I believe that's what the Swedish call Sweden, yes?)

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 13:16:58

single delete will be like this:-

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY SID, Designation, EventP ORDER BY SID, Designation, EventP) AS Seq,
SUM(CASE WHEN EventP='' THEN 0 ELSE 1 END) OVER (PARTITION BY SID, Designation) AS Occurance
FROM #Table1
)t
WHERE t.EventP='' 
AND (Occurance>0 OR AND Seq>1)

EDIT : missed order by inside row_number()

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:19:36

quote:

Originally posted by visakh16

single delete will be like this:-

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY SID, Designation, EventP) AS Seq,
SUM(CASE WHEN EventP='' THEN 0 ELSE 1 END) OVER (PARTITION BY SID, Designation) AS Occurance
FROM #Table1
)t
WHERE t.EventP='' 
AND (Occurance>0 OR AND Seq>1)

ROW_NUMBER() function in a SQL Server 2000 query?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:20:32

row_number() also need the order by part.

E 12°55'05.63"
N 56°04'39.26"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-27 : 13:22:32

When did SQL Server 2000 get Row Number() function? Was this an add-on or upgrade?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 13:26:42

quote:
Originally posted by Xerxes

When did SQL Server 2000 get Row Number() function? Was this an add-on or upgrade?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Oops didnt realise its a sql 2000 forum sorry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources