Removing embedded duplicate rows

Question

Post reply

Removing embedded duplicate rows

Viewing 11 posts - 46 through 55 (of 55 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

noeld

SSC Guru

Points: 96590

August 16, 2005 at 8:52 am

#582485

You should open a new thread !!!

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Wanted to tell him that but that thread is opened 5 times/week so one less time shouldn't hurt anyone .

bob day SSC Enthusiast Points: 148 More actions · Answer 3

How about?

Declare @cnt int,

@PictureSetID int,

@Height int,

@Width int,

@PictureID int,

@Description varchar(200),

@FileName varchar(200)

--Create Temp output table

Create TABLE #tmp

(PictureSetID int,

Height int,

Width int,

PictureID int,

Description varchar(200),

FileName varchar(200))

--Create cursor of all records (dups included)

Declare getallrecords cursor local static For

SELECT TOP 25 A.PictureSetID, B.Height, B.Width, B.PictureID, B.Description, B.FileName

FROM FEATS_PictureSetItem A, FEATS_Picture B

WHERE A.PictureSetID = 3 AND

A.PictureID not in (0) AND (A.PictureID = B.PictureID)

GROUP BY B.Description, A.PictureSetID, B.Height, B.Width, B.PictureID, B.FileName

ORDER BY NEWID()

--open the cursor

Open getallrecords

-- Perform the first fetch.

Fetch next from getallrecords into @PictureSetID,@Height,@Width,@PictureID,@Description,@FileName

--Loop Thru RecordSet

While @@fetch_status=0

Begin

--Only INSERT non dup records

If (SELECT count(*) from #tmp Where Description LIKE substring(@description, 1, (len(description)-1))+ '%' ) = 0

BEGIN

INSERT #tmp (PictureSetID, Height, Width, PictureID, Description, FileName) Values(@PictureSetID,@Height,@Width,@PictureID,@Description,@FileName)

END

--get next record

Fetch next from getallrecords into @PictureSetID,@Height,@Width,@PictureID,@Description,@FileName

End

--cursor housekeeping

Close getallrecords

Deallocate getallrecords

--Display the non-dup result table

Select * from #tmp

--temp table housekeeping

Drop Table #tmp

GO

noeld SSC Guru Points: 96590 More actions · Answer 4

don't get me started with the unnecessary use of cursors and where is

@myVar='10,80,90' used in there ?

* Noel

bob day SSC Enthusiast Points: 148 More actions · Answer 5

in here:

A.PictureID not in (0)

A.PictureID not in (10, 20, 30)

or

A.PictureID not in (@myVar)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Yup there's just no need to use a cursor to split the values with the cursor... I don't wanna get started on that either .

bob day SSC Enthusiast Points: 148 More actions · Answer 7

blah,blah, blah....I'm done & I can leave at 5:00!

exec sp_FEATS_pictureX 6, '471,499,460'

CREATE PROCEDURE sp_FEATS_PictureX

@Concept int,

@NotIn varchar(100)

AS

CREATE TABLE #strings (str int NOT NULL)

exec charlist_to_table_sp @NotIN

Declare @cnt int,

@PictureSetID int,

@Height int,

@Width int,

@PictureID int,

@Description varchar(200),

@FileName varchar(200)

--Create Temp output table

Create TABLE #tmp

(PictureSetID int,

Height int,

Width int,

PictureID int,

Description varchar(200),

FileName varchar(200))

--Create cursor of all records (dups included)

Declare getallrecords cursor local static For

SELECT TOP 25 A.PictureSetID, B.Height, B.Width, B.PictureID, B.Description, B.FileName

FROM FEATS_PictureSetItem A, FEATS_Picture B

WHERE A.PictureSetID = @Concept AND

A.PictureID not in (Select * from #strings) AND (A.PictureID = B.PictureID)

GROUP BY B.Description, A.PictureSetID, B.Height, B.Width, B.PictureID, B.FileName

ORDER BY NEWID()

--open the cursor

Open getallrecords

-- Perform the first fetch.

Fetch next from getallrecords into @PictureSetID,@Height,@Width,@PictureID,@Description,@FileName

--Loop Thru RecordSet

While @@fetch_status=0

Begin

--Only INSERT non dup records

If (SELECT count(*) from #tmp Where Description LIKE substring(@description, 1, (len(description)-1))+ '%' ) = 0

BEGIN

INSERT #tmp (PictureSetID, Height, Width, PictureID, Description, FileName) Values(@PictureSetID,@Height,@Width,@PictureID,@Description,@FileName)

END

--get next record

Fetch next from getallrecords into @PictureSetID,@Height,@Width,@PictureID,@Description,@FileName

End

--cursor housekeeping

Close getallrecords

Deallocate getallrecords

--Display the non-dup result table

Select * from #tmp

--temp table housekeeping

Drop Table #tmp

Drop Table #strings

GO

^^^^^^^^^^^^^^^^^^^^^^^^^^^^

CREATE PROCEDURE charlist_to_table_sp

@list ntext,

@delimiter char(1) = N',' AS

DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

SET NOCOUNT ON

SELECT @textpos = 1, @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SELECT @chunklen = 4000 - datalength(@leftover) / 2

SELECT @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SELECT @textpos = @textpos + @chunklen

SELECT @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0

BEGIN

SELECT @tmpval = left(@tmpstr, @pos - 1)

SELECT @tmpval = ltrim(rtrim(@tmpval))

INSERT #strings(str) VALUES (@tmpval)

SELECT @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SELECT @pos = charindex(@delimiter, @tmpstr)

END

SELECT @leftover = @tmpstr

END

INSERT #strings(str) VALUES(ltrim(rtrim(@leftover)))

GO

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Good, now we can close this thing. Make sure you look up the split versions we showed you as they're set based and faster than this one.

bob day SSC Enthusiast Points: 148 More actions · Answer 9

I have a headache, I'm going to lunch and who cares if it's faster - it's not going on the Space Shuttle...thanks.

noeld SSC Guru Points: 96590 More actions · Answer 10

sure I would love to see your app scale! ... it will be good for you

Then I will see you comming for help and other people cursing the developer for performance issues, laughing at the code ... it won't be good for you

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Wow, that's much more polite than what I wanted to say. Let's leave it at that for now... also I'm very glad you don't work for N.A.S.A, they have enough problem with their shuttles as is.