Removing embedded duplicate rows

Question

Removing embedded duplicate rows

Viewing 15 posts - 31 through 45 (of 55 total)

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

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

This is not rocket science. A guy has a stupid kid game he sells on a cd. I got hired to write a scaled-down web demo of it. The XML file it uses has the data laid out that way. I imported it to SQL and presto..this stupid-little-kinda problem that pops up once and awhile. I CAN fix the problem in ASP but I wanted a slick SQL solution...not change the figging database. Maybe it just can't be done.

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

That's what we told you in a not too beautiful way. Good luck with that project.

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 3

"Some of you guys need to date more!"

Gee I knew that. Remi plays pool but I could use a few phone numbers.

Mike

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

I would date more, but it would interfere with my work, pool and or social life so I refrain from it .

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

You know what would be funny?...if this was a sample question on a job interview. Given the data & structure of the table, there must be a way to accomplish this in SQL Server. Next week I will figure it out and post it here! I'm too tired today!

'It's not necessary to knock the whole building down to change a light switch on the third floor!'

noeld SSC Guru Points: 96590 More actions · Answer 6

This is a classic scenario of:

Fast,Cheap,Good --> Pick Two

I have been in that possiton quite a bit and even after my recommendation I have implemented the WRONG approach because the one who was signing my pay check said so!!! Therefore I don't blame you

And about the Dating issue.... I would make no comments either

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

I wouldn't make any comments on the dating issue either except to say that I've told remi only about 10 times in as many weeks to G.A.L....

**ASCII stupid question, get a stupid ANSI !!!**

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

Bob, I know this can be done, but that can't really be error proof (good pick). I know you can do it on the server, but you basically combine what you want to do in asp and what we provided in sql server which is a worst practice. If you think this is the best path for this contract, I have no problem with that. But I'll keep answering the questions like I do now because I want to pick GOOD, FAST and cheap if possible.

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

I'm gonna say this one last time Sushila. I wanna freaking turn PRO and make a living a playing pool, so THAT'S MY LIFE.

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

okay remi (sheesh!)...backing off (& down) real slow here...SORRY if I've stepped on any toes...

**ASCII stupid question, get a stupid ANSI !!!**

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

Like my friend told me once... it's not what you said.. it's the accumulation...

sushila SSC-Dedicated Points: 35293 More actions · Answer 12

okay - i hate being yelled at so that's the last time you'll hear it from me!

**ASCII stupid question, get a stupid ANSI !!!**

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

Sorry if I offended you but I don't like having my life thrown in the dirt.

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

Are you girls still out there?

How would you pass a sting of numbers to a stored proc...ie

@myVar='10,80,90'

then in the sp use as

Select ..... NOT IN @myVar

It don't work like this..

-Bob

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

IF Object_id('fnSplit_Set') > 0

DROP FUNCTION dbo.fnSplit_Set

GO

IF Object_id('Numbers') > 0

DROP TABLE dbo.Numbers

GO

CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))

GO

INSERT INTO dbo.Numbers (dude)

SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80

GO

ALTER TABLE dbo.Numbers

DROP COLUMN dude

GO

--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers

--1, 8000

GO

CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))

RETURNS TABLE

WITH SCHEMABINDING

AS

Return

Select dtSplitted.EachID, dtSplitted.Rank from (

SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),

CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID

, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank

FROM dbo.Numbers N

WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter

AND PkNumber 0

GO

DECLARE @Ids as varchar(8000)

SET @IDS = ''

Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds

SET @IDS = left(@IDS, ABS(len(@IDS) - 1))

PRINT @IDS

Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name

Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank

--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.