Person data- Multiple rows due to more than 1 name. Automatic Numbering of rows

Question

Post reply

Person data- Multiple rows due to more than 1 name. Automatic Numbering of rows

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
December 19, 2008 at 3:48 am

#203068

I have the following script
DROP TABLE dbo.SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY
CREATE TABLE dbo.SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY
([fi pin] INT, Alias varchar(100))
INSERT INTO dbo.SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY
([fi pin], Alias)
SELECT 118007, 'Sara Jane smith'UNION ALL
SELECT 118007, 'Sarah Smith'UNION ALL
SELECT 118007, 'Sara Jayne Smith'UNION ALL
SELECT 118007, 'Sara Smith'UNION ALL
SELECT 118008, 'John Good'UNION ALL
SELECT 118008, 'Jon Goode'UNION ALL
SELECT 118008, 'Jonny Good'UNION ALL
SELECT 118008, 'Jonny Goode'UNION ALL
SELECT 118009, 'Debora Ellis'UNION ALL
SELECT 118009, 'Debbie Ellis'UNION ALL
SELECT 118010, 'Jon Junior'
I am wanting to run script that brings back the following (Numbering the names)
118007, 'Sara Jane smith',1
118007, 'Sarah Smith' ,2
118007, 'Sara Jayne Smith' ,3
118007, 'Sara Smith' ,4
118008, 'John Good',1
118008, 'Jon Goode' ,2
118008, 'Jonny Good' ,3
118008, 'Jonny Goode',4
118009, 'Debora Ellis',1
118009, 'Debbie Ellis',2
118010, 'Jon Junior',1
Any help would be much apprieciated. Im thinking you start with creating a clustered index but Im not sure....
CREATE CLUSTERED INDEX [Alias_Numbering] ON [dbo].[SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY]([Fi Pin], [Alias ]) ON [PRIMARY]
:blink: Debbie

Viewing 15 posts - 1 through 14 (of 14 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

[font="Courier New"]DROP TABLE #SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

CREATE TABLE #SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

([fi pin] INT, Alias VARCHAR(100))

INSERT INTO #SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

([fi pin], Alias)

SELECT 118007, 'Sara Jane smith'UNION ALL

SELECT 118007, 'Sarah Smith'UNION ALL

SELECT 118007, 'Sara Jayne Smith'UNION ALL

SELECT 118007, 'Sara Smith'UNION ALL

SELECT 118008, 'John Good'UNION ALL

SELECT 118008, 'Jon Goode'UNION ALL

SELECT 118008, 'Jonny Good'UNION ALL

SELECT 118008, 'Jonny Goode'UNION ALL

SELECT 118009, 'Debora Ellis'UNION ALL

SELECT 118009, 'Debbie Ellis'UNION ALL

SELECT 118010, 'Jon Junior'

SELECT *, ROW_NUMBER() OVER(PARTITION BY [fi pin] ORDER BY Alias) AS 'Row Number'

FROM #SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

[/font]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Thanks for that. Ive tried it but Im getting

Msg 195, Level 15, State 10, Line 1

'ROW_NUMBER' is not a recognized function name.

Debbie

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Debbie Edwards (12/19/2008)
Thanks for that. Ive tried it but Im getting
Msg 195, Level 15, State 10, Line 1
'ROW_NUMBER' is not a recognized function name.
Debbie

It's a SQL Server 2005 function, and this is a SQL Server 2005 forum section - are you using a different version?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Im on SQL Server 2005.

Is there another way around this issue without using Partition?

Debbie

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Debbie Edwards (12/19/2008)
Im on SQL Server 2005.
Is there another way around this issue without using Partition?
Debbie

Debbie, I can see from some of your earlier posts that 2k5-specific code invariably fails in your environment. It's been attributed to compatibility mode, but just to be sure, could you please post the result of this statement?

SELECT @@VERSION

Many thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 6

Ahhhh

Even though Im in SSMS Im using

Microsoft SQL Server Management Studio9.00.3042.00

Microsoft Analysis Services Client Tools2005.090.3042.00

Microsoft Data Access Components (MDAC)2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML2.6 3.0 4.0 5.0 6.0

Microsoft Internet Explorer6.0.2900.2180

Microsoft .NET Framework2.0.50727.42

Operating System5.1.2600

I think thats why last time we fo0und another way around it.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Debbie Edwards (12/19/2008)
Ahhhh
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I think thats why last time we fo0und another way around it.

Aha! There we go. The server you're connected to, which is running your code, is SQL Server 2000.

I'm afraid it's back to those ol' "running totals", Debbie. If you go through your posts, you've been talked through how to do this with samples. Have a look, if you're still stuck, then we'll give you a hand. Be sure to post in the SQL Server 2000 section 😎

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 8

We may not be all be psychic, but apparently some of us are detectives in our spare time :P.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 9

Thanks :ermm:

I think the best thing I can do is try to figure out another simpler way, Im out of my league a bit here.

Im hoping there may be dates for each Alias name so i can grab the latest one in that way. I had a look at the last mail I did in regards to this and tried some of it out but couldnt get it to work at all.

Apologoes if I added a topic in the wrong area.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Debbie Edwards (12/19/2008)
Thanks :ermm:
I think the best thing I can do is try to figure out another simpler way, Im out of my league a bit here.
Im hoping there may be dates for each Alias name so i can grab the latest one in that way. I had a look at the last mail I did in regards to this and tried some of it out but couldnt get it to work at all.
Apologoes if I added a topic in the wrong area.

Lemme guess...there are multiple instances of each name and you're working on a dedupe exercise?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 11

Thats right so I eventually get 1 row of data instead of 4

fi Id Alias

118007, 'Sara Jane smith/Sarah Smith/Sara Jayne Smith/Sara Smith'

Im keeping my fingers crossed that I can do something like take the last alias added.

Debbie

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

If you can figure out the rules, it shouldn't be too hard to figure out the code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 13

Debbie Edwards

SSCrazy Eights

Points: 9217

February 7, 2012 at 8:35 am

#1443840

Whoops Sorry I posted into the wrong thread....

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 14

The way I would solve that problem is to use a temp table.

Step 1: Insert all the names into a temp table that contains an IDENTITY column, sorted by the [fi pin] so that each pin is in sequence.

Step 2: Determine the MIN sequence number for each [fi pin].

Step 3: JOIN the MIN data back to the main data based on [fi pin]

Step 4: Subtract the MIN sequence number from the record (should give you the offset), then add 1.

-- Table used for creating a sequence number for each record.

CREATE-- DROP

TABLE#TempNames

(

[fi pin]INTEGERNOT NULL,

AliasVARCHAR(100)NOT NULL,

SequenceNumINTEGERIDENTITY(1,1)

)

-- Create the records with sequence numbers.

INSERT

INTO#TempNames ([fi pin], Alias)

SELECT[fi pin], Alias

FROMdbo.SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

ORDER BY [fi pin], Alias

SELECTt.[fi pin], t.Alias, (t.SequenceNum - s.MinSeq) + 1 AS SequenceNum

FROM#TempNames t

JOIN(-- Find the lowest sequence number for each [fi pin]

SELECT[fi pin], MIN(SequenceNum) AS MinSeq

FROM#TempNames

GROUP BY [fi pin]

) s

ONs.[fi pin] = t.[fi pin]

ORDER BY t.[fi pin], t.Alias