hey all,
i have a table with about 1300 rows
the records are grouped by a type and i want to
assign each type a numbering sequence. what's the best way to do this?
For instance,
Rec1 Type1 Seq1
Rec2 Type1 Seq2
Rec3 Type2 Seq1
Rec4 Type2 Seq2
Rec5 Type2 Seq3
The seq columns is what i'm trying to incorporate.
thanks,
rodcharI assume from your post that the records are already sequenced globally...
Then you can do this for output only with a query
Select RecSeq, Type,
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq) TypeSeq
From TableName T
Or if you have another column in the table that you want to actually
populate with the value
Then
Update T Set TypeSeq =
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq)
From TableName T
"rodchar" wrote:
> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||We need to know if there is another column we can using to uniquely identity
a row in a group.
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"rodchar" wrote:
> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||I don't think I'm doing something right here cause it's not working.
Please let me explain a different way to make sure:
Rec1 Type Seq#
-- -- --
1 A
3 B
2 A
4 A
5 B
After should look like the following:
Rec1 Type Seq#
-- -- --
1 A 1
2 A 2
4 A 3
3 B 1
5 B 2
The Seq# field is a new field that I need numbered sequentially by Types
So you're saying that the update statement in the above reply should do this
?
thanks,
rodchar
"CBretana" wrote:
> I assume from your post that the records are already sequenced globally...
> Then you can do this for output only with a query
> Select RecSeq, Type,
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq) TypeSeq
> From TableName T
> Or if you have another column in the table that you want to actually
> populate with the value
> Then
> Update T Set TypeSeq =
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq)
> From TableName T
>
> "rodchar" wrote:
>|||The "new" field that you want populated must already exist in the Table
first. Have you added it? If you have named it "SeqNo" then
Update T Set SeqNo=
(Select Count(*) From TableName
Where Type = T.Type
And Rec1 <= T.Rec1)
From TableName T
What error are you getting ?
"rodchar" wrote:
> I don't think I'm doing something right here cause it's not working.
> Please let me explain a different way to make sure:
> Rec1 Type Seq#
> -- -- --
> 1 A
> 3 B
> 2 A
> 4 A
> 5 B
> After should look like the following:
> Rec1 Type Seq#
> -- -- --
> 1 A 1
> 2 A 2
> 4 A 3
> 3 B 1
> 5 B 2
> The Seq# field is a new field that I need numbered sequentially by Types
> So you're saying that the update statement in the above reply should do th
is?
> thanks,
> rodchar
> "CBretana" wrote:
>|||I'm not getting an error message however the value for the new field contain
s
that maximum number (1372) for all the records:
Here's my actual statement:
UPDATE Records
SET SeqNo =
(SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department AND
RecID <= Records.RecID)
FROM Records
So, here we have
RecID (like Rec1 is just an autonumber field)
Department is the type
"CBretana" wrote:
> The "new" field that you want populated must already exist in the Table
> first. Have you added it? If you have named it "SeqNo" then
> Update T Set SeqNo=
> (Select Count(*) From TableName
> Where Type = T.Type
> And Rec1 <= T.Rec1)
> From TableName T
>
> What error are you getting ?
>
> "rodchar" wrote:
>|||On Tue, 5 Apr 2005 11:55:03 -0700, rodchar wrote:
>I'm not getting an error message however the value for the new field contai
ns
>that maximum number (1372) for all the records:
>Here's my actual statement:
>UPDATE Records
>SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
>RecID <= Records.RecID)
>FROM Records
(snip)
You omitted the table alias from CBretana's solution. Try this one
instead (not exactly the same as CBretana's suggestion - I changed it to
use ANSI-standard instead of proprietary Transact-SQL syntax):
UPDATE Records
SET SeqNo = (SELECT COUNT(*)
FROM Records AS R
WHERE R.Department = Records.Department
AND R.RecID <= Records.RecID)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Because you're not using an alias for the tablename in the outer query... Th
e
way you wrote it,
UPDATE Records SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department
AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
FROM Records
The token <Records> in the Last part of the subquery's where clause refers
to the Records table IN the subquery, so you're saying Where RecID in The
Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s to
ask
" Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That's
why you need to use Table ALias
UPDATE R SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = R.Department
AND RecID <= R.RecID)
FROM Records As R
"rodchar" wrote:
> I'm not getting an error message however the value for the new field conta
ins
> that maximum number (1372) for all the records:
> Here's my actual statement:
> UPDATE Records
> SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
> RecID <= Records.RecID)
> FROM Records
> So, here we have
> RecID (like Rec1 is just an autonumber field)
> Department is the type
>
> "CBretana" wrote:
>|||thank you all, I give a try and let you know.
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>|||That's awesome, hey any recommendations on a good book or how to get up to
speed in knowing how to write statements like these?
thanks so much for everyone's help here,
rodchar
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>
Showing posts with label numbers. Show all posts
Showing posts with label numbers. Show all posts
Sunday, March 11, 2012
Assigning Group Numbers for millions of row
I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3
I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. I now that this is not an efficient
code.
INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
dist FLS is distinct First Name, last Name and SSN table from the
people table.
I have posted part of this question, schema one w
ago. Please refer
this thread.
http://groups.google.com/group/comp...6eb380b5f2e6de6Basically, this is just a query that sorts or groups on a CASE function.
However, the catch is how we want to classify different names as "similar".
I would say that two rows should be considered similar if they have the same
SSN and the names start with the same first letter. Instead of a group
number, let's do a group code which conists of those 2 characters. Since
fname and lname may be transposed, the lowest of the 2 characters will be
encoded first followed by the highest character.
fname lname SSN grpCode
-- -- -- --
J S 1234 JS
J Smith 1234 JS
S John 1234 JS
John Smith 1111 JS
John Smith 1234 JS
Smith John 1234 JS
Dan Brown 1234 BD
select lname, fname, SSN, grpCode
from
(
select
fname,
lname,
SSN,
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1)
else left(lname,1)
end as grpCode
--
from
distFLS
) as x
order by
SSN,
grpCode,
fname,
lname
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w ago. Please refer
> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>|||The group code calculation returns only with one letter.
I have added this code on it.
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1) +
left(lname,1)
else left(lname,1) +left(fname,1)
end as grpCode
--|||I didn't run it on my end.
Thanks.
<jacob.dba@.gmail.com> wrote in message
news:1143487218.339033.116420@.v46g2000cwv.googlegroups.com...
> The group code calculation returns only with one letter.
> I have added this code on it.
> -- Here we calculate the grpCode:
> case
> when left(fname,1) <= left(lname,1) then left(fname,1) +
> left(lname,1)
> else left(lname,1) +left(fname,1)
> end as grpCode
> --
>|||I fogot to mention that some of the records have middle name entered
in place of first name or last name.
fname mname lname ssn
John coleman smith 1234
john smith coleman 1234
john S coleman 1234
John C Smith 1234
John Smith 1234
John-coleman Smith 1234
Smith John 1234
During the grouping process I am concerned only about fname,lname,
ssn.(no need of middle name). If there is other suggestion to include
columns I am happy to accept.
I have the idea to assign groups if one of the initial of the names is
similar with the others considering that the SSN is the same. that
means if SSN is equal and if J or S or C are there as an initial in the
names, we can say they are in the same group.|||Just revise the case function as needed, but the concept is the same.
<jacob.dba@.gmail.com> wrote in message
news:1143490139.170226.286890@.g10g2000cwb.googlegroups.com...
> I fogot to mention that some of the records have middle name entered
> in place of first name or last name.
> fname mname lname ssn
> John coleman smith 1234
> john smith coleman 1234
> john S coleman 1234
> John C Smith 1234
> John Smith 1234
> John-coleman Smith 1234
> Smith John 1234
> During the grouping process I am concerned only about fname,lname,
> ssn.(no need of middle name). If there is other suggestion to include
> columns I am happy to accept.
> I have the idea to assign groups if one of the initial of the names is
> similar with the others considering that the SSN is the same. that
> means if SSN is equal and if J or S or C are there as an initial in the
> names, we can say they are in the same group.
>|||Consider using Integration Services as that tool has a Fuzzy Lookup and
Fuzzy Grouping tasks that were specifically designed for this type of work.
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w ago. Please refer
> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3
I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. I now that this is not an efficient
code.
INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
dist FLS is distinct First Name, last Name and SSN table from the
people table.
I have posted part of this question, schema one w
ago. Please referthis thread.
http://groups.google.com/group/comp...6eb380b5f2e6de6Basically, this is just a query that sorts or groups on a CASE function.
However, the catch is how we want to classify different names as "similar".
I would say that two rows should be considered similar if they have the same
SSN and the names start with the same first letter. Instead of a group
number, let's do a group code which conists of those 2 characters. Since
fname and lname may be transposed, the lowest of the 2 characters will be
encoded first followed by the highest character.
fname lname SSN grpCode
-- -- -- --
J S 1234 JS
J Smith 1234 JS
S John 1234 JS
John Smith 1111 JS
John Smith 1234 JS
Smith John 1234 JS
Dan Brown 1234 BD
select lname, fname, SSN, grpCode
from
(
select
fname,
lname,
SSN,
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1)
else left(lname,1)
end as grpCode
--
from
distFLS
) as x
order by
SSN,
grpCode,
fname,
lname
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w
ago. Please refer> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>|||The group code calculation returns only with one letter.
I have added this code on it.
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1) +
left(lname,1)
else left(lname,1) +left(fname,1)
end as grpCode
--|||I didn't run it on my end.
Thanks.
<jacob.dba@.gmail.com> wrote in message
news:1143487218.339033.116420@.v46g2000cwv.googlegroups.com...
> The group code calculation returns only with one letter.
> I have added this code on it.
> -- Here we calculate the grpCode:
> case
> when left(fname,1) <= left(lname,1) then left(fname,1) +
> left(lname,1)
> else left(lname,1) +left(fname,1)
> end as grpCode
> --
>|||I fogot to mention that some of the records have middle name entered
in place of first name or last name.
fname mname lname ssn
John coleman smith 1234
john smith coleman 1234
john S coleman 1234
John C Smith 1234
John Smith 1234
John-coleman Smith 1234
Smith John 1234
During the grouping process I am concerned only about fname,lname,
ssn.(no need of middle name). If there is other suggestion to include
columns I am happy to accept.
I have the idea to assign groups if one of the initial of the names is
similar with the others considering that the SSN is the same. that
means if SSN is equal and if J or S or C are there as an initial in the
names, we can say they are in the same group.|||Just revise the case function as needed, but the concept is the same.
<jacob.dba@.gmail.com> wrote in message
news:1143490139.170226.286890@.g10g2000cwb.googlegroups.com...
> I fogot to mention that some of the records have middle name entered
> in place of first name or last name.
> fname mname lname ssn
> John coleman smith 1234
> john smith coleman 1234
> john S coleman 1234
> John C Smith 1234
> John Smith 1234
> John-coleman Smith 1234
> Smith John 1234
> During the grouping process I am concerned only about fname,lname,
> ssn.(no need of middle name). If there is other suggestion to include
> columns I am happy to accept.
> I have the idea to assign groups if one of the initial of the names is
> similar with the others considering that the SSN is the same. that
> means if SSN is equal and if J or S or C are there as an initial in the
> names, we can say they are in the same group.
>|||Consider using Integration Services as that tool has a Fuzzy Lookup and
Fuzzy Grouping tasks that were specifically designed for this type of work.
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w
ago. Please refer> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>
Assigning group numbers for millions of data
I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3
I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. I now that this is not an efficient
code.
INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
dist FLS is distinct First Name, last Name and SSN table from the
people table.
I have posted part of this question, schema one week ago. Please refer
this thread.
http://groups.google.com/group/comp...6eb380b5f2e6de6I forgot to mention that some of the records have middle name entered
in place of first name or last name.
fname mname lname ssn
John coleman smith 1234
john smith coleman 1234
john S coleman 1234
John C Smith 1234
John Smith 1234
John-coleman Smith 1234
Smith John 1234
During the grouping process I am concerned only about fname,lname,
ssn.(no need of middle name). If there is other suggestion to include
columns I am happy to accept.
I have the idea to assign groups if one of the initial of the names is
similar with the others considering that the SSN is the same. that
means if SSN is equal and if J or S or C are there as an initial in the
names, we can say they are in the same group.
Reply
jacob.dba@.gmail.com wrote:
> I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
> I have posted part of this question, schema one week ago. Please refer
> this thread.
> http://groups.google.com/group/comp...6eb380b5f2e6de6|||(jacob.dba@.gmail.com) writes:
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
What if you have both John Smith and Southerland Jane? Are the
same person or not?
This looks like a very difficult task, and the fact that you have
800 million rows certainly does not help to make it easier.
I think you need to scrap the idea you got from Itzik. My gut feeling
say that it will not scale.
Here is a very simple-minded solution where I've assumed that as
long as any combination of initials match, it's the same group.
CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE #initials (ssn int NOT NULL,
fname varchar(32) NOT NULL,
lname varchar(32) NOT NULL,
initials char(2) NOT NULL)
go
CREATE TABLE #ssnmania (ident int NOT NULL,
ssn int NOT NULL,
initials char(2) NOT NULL,
PRIMARY KEY(ssn, initials))
go
INSERT #initals (ssn, fname, lname, initials)
SELECT DISTINCT ssn, fname, lname,
CASE WHEN fname < lname
THEN substring(fname, 1, 1) + substring(lname, 1, 1)
ELSE substring(lname, 1, 1) + substring(fname, 1, 1)
END
FROM TU_People_Data
go
INSERT #ssnmania (ssn, initials)
SELECT DISTINCT ssn, initials
FROM #initials
go
SELECT i.ssn, i.fname, i.lname, i.initials, groupno = s.ident
FROM #initials i
JOIN #ssnmania s ON i.ssn = s.ssn
AND s.initials = i.initials
go
DROP TABLE #initials, #ssnmania, TU_People_Data
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.
I have tried this procedure in the morning and it solves half of my
problem.
let me start by answering your question.
>What if you have both John Smith and Southerland Jane? Are the
> same person or not?
If these guys' SSN is the same, they are considered to be in the the
same group.
I am willing to take the chance that John Smith, Southerland Jane and
Jack Sam with similar SSN has slim chance to occur. if they exist,
they are gouped in one group number.
>>regarding your solution
In my table some of the rows for one person are displayed like this.
1.John Coleman Smith 1111 JS
2.John Smith Coleman 1111 CJ
3.Coleman John Smith 1111 CS
4.John-coleman Smith 1111 JS
5. Smith John 1111 JS
6.John Smith 2222 JS
7.J Smith 1111 JS
8 Jack Sam 3333 JS
you can see that all this guys can be grouped in the same group
name(except the 6th and 8th). I see that SSN is the major factor to
identify the groups.
So once SSN is the same then the intitals has to be one or two of the
three J or S or C.
Erland Sommarskog wrote:
> (jacob.dba@.gmail.com) writes:
> > I want to assign group number according to this business logic.
> > 1. Records with equal SSN and (similar first name or last name) belong
> > to the same group.
> > John Smith 1234
> > Smith John 1234
> > S John 1234
> > J Smith 1234
> > John Smith and Smith John falls in the same group Number as long as
> > they have similar SSN.
> > This is because I have a record of equal SSN but the first name and
> > last name is switched because of people who make error inserting last
> > name as first name and vice versa. John Smith and Smith John will have
> > equal group Name if they have equal SSN.
> > 2. There are records with equal SSN but different first name and last
> > name. These belong to different group numbers.
> > Equal SSN doesn't guarantee equal group number, at least one of the
> > first name or last name should be the same. John Smith and Dan Brown
> > with equal SSN=1234 shouldn't fall in the same group number.
> What if you have both John Smith and Southerland Jane? Are the
> same person or not?
> This looks like a very difficult task, and the fact that you have
> 800 million rows certainly does not help to make it easier.
> I think you need to scrap the idea you got from Itzik. My gut feeling
> say that it will not scale.
> Here is a very simple-minded solution where I've assumed that as
> long as any combination of initials match, it's the same group.
>
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TABLE #initials (ssn int NOT NULL,
> fname varchar(32) NOT NULL,
> lname varchar(32) NOT NULL,
> initials char(2) NOT NULL)
> go
> CREATE TABLE #ssnmania (ident int NOT NULL,
> ssn int NOT NULL,
> initials char(2) NOT NULL,
> PRIMARY KEY(ssn, initials))
> go
> INSERT #initals (ssn, fname, lname, initials)
> SELECT DISTINCT ssn, fname, lname,
> CASE WHEN fname < lname
> THEN substring(fname, 1, 1) + substring(lname, 1, 1)
> ELSE substring(lname, 1, 1) + substring(fname, 1, 1)
> END
> FROM TU_People_Data
> go
> INSERT #ssnmania (ssn, initials)
> SELECT DISTINCT ssn, initials
> FROM #initials
> go
> SELECT i.ssn, i.fname, i.lname, i.initials, groupno = s.ident
> FROM #initials i
> JOIN #ssnmania s ON i.ssn = s.ssn
> AND s.initials = i.initials
> go
> DROP TABLE #initials, #ssnmania, TU_People_Data
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||(jacob.dba@.gmail.com) writes:
> I have tried this procedure in the morning and it solves half of my
> problem.
And the other half is? :-) I did not include the middle initial, because
I did not see that post until later.
But I guess that you could extend the logic that I posted to handle
the middle initial as well.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you need a function that takes the first character from first name,
last name, and middle initial, and sorts them. Call it "SortInit"
So, pass "Sam Alfred Jones" and it passes back "AJS". Likewise,
"Jones Alfred Sam" is returned as "AJS".
then, create your temp table and populate it with SSN and Sortinit().
then alter table on your temp table and add an identity column.
Then make your "temp table" a permanent one, as your business rules
will change, and fundamentally what you are doing is looking for
"duplicate rows" and grouping them, and this is almost always a
multiple pass project.
Thursday, March 8, 2012
Assign Set Numbers
I have a raw data set like so:
I need to identify where the time stamps for PB and PL are sets. They all have the same OrdNumber and RoomNum and yet I need to capture the time difference for each set of PB and PL. I will use the accumulative time from these sets for this RoomNum in a report.
I have an idea that should work, but can't get the syntax right. If I were to keep these in ascending order and then assign a number for each event code separately I should have the same number assigned for each code which would serve as a set number. So, if I were to create a variable +1 that would look at "PB" and then reset when the OrderNum changes. Then do the same for the 'PL'.
Could I do that step as an Update to a designated "Set" field from a temp table in my code?
My end result should have the number 1 in the first two I have in bold type, then 2, etc. I have very little experience at this level and could use some help.
Thank you.
You can do exactly the same with subqueries and creating uniqueids with ROW_NUMBER(), try this:
Code Snippet
select subPB.OrdNumber, subPB.RoomNum, datediff(minute, subPB.eventdate, subPL.eventdate) as TimeElapsed
from
(select OrdNumber,EventDate,EventCode,RoomNum, ROW_NUMBER() over ( order by Eventdate)as id
from tbltest2 where Eventcode='PB'
) subPB,
(select OrdNumber,EventDate,EventCode,RoomNum, Row_number() over( order by Eventdate)as id
from tbltest2 where Eventcode='PL'
) subPL
where subPB.ordnumber=subPL.ordnumber and subPB.roomnum = subPL.roomnum
and subPB.id=subPL.id
I can't get this to work because it is not accepting row_number() and 'Over'. I am working in Embarcardero Sybase Rapid SQL if that helps.
|||do you know what your underlying DataBase is? The code I posted is for SQL Server 2005.
If it's some other database, you need to figure how to generate ids for that DB instead of using "ROW_Number() over"
|||How about this query..
Code Snippet
CreateTable #data(
[OrdNumber]Varchar(100),
[EventDate]dateTime,
[EventCode]Varchar(100),
[RoomNum]int
);
InsertInto #dataValues('7900059-1','5/1/07 6:41 AM','PB','3100');
InsertInto #dataValues('7900059-1','5/1/07 6:49 AM','PL','3100');
InsertInto #dataValues('7900059-1','5/1/07 8:09 AM','PB','3100');
InsertInto #dataValues('7900059-1','5/1/07 8:16 AM','PL','3100');
InsertInto #dataValues('7900059-1','5/1/07 8:53 AM','PB','3100');
InsertInto #dataValues('7900059-1','5/1/07 9:02 AM','PL','3100');
InsertInto #dataValues('7900059-1','5/1/07 10:04 AM','PB','3100');
InsertInto #dataValues('7900059-1','5/1/07 10:16 AM','PL','3100');
InsertInto #dataValues('7900059-1','5/1/07 12:20 PM','PB','3100');
InsertInto #dataValues('7900059-1','5/1/07 12:37 PM','PL','3100');
InsertInto #dataValues('7900059-1','5/1/07 1:59 PM','PB','3100');
InsertInto #dataValues('7900059-1','5/1/07 2:13 PM','PL','3100');
InsertInto #dataValues('7900059-1','5/2/07 6:49 AM','PB','3100');
InsertInto #dataValues('7900059-1','5/2/07 6:59 AM','PL','3100');
Select*,Identity(int,1,1) RowIdInto #PBFrom #DataWhere [EventCode]='PB'ORDERBY 2
Select*,Identity(int,1,1) RowIdInto #PLFrom #DataWhere [EventCode]='PL'ORDERBY 2
Select
*
from
#PB PB
Join #PL PL
On
PB.RowId=PL.RowId
|||Hi Manivannan.D.Sekaran,
This method is not reliable.
The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause
http://support.microsoft.com/kb/273586
AMB
|||Thank you very much AMB.. Its really surprise for me (I learnt new thing)
Then, How about this approach..
Code Snippet
Create Table #data (
[OrdNumber] Varchar(100) ,
[EventDate] dateTime ,
[EventCode] Varchar(100) ,
[RoomNum] int
);
Insert Into #data Values('7900059-1','5/1/07 6:41 AM','PB','3100');
Insert Into #data Values('7900059-1','5/1/07 6:49 AM','PL','3100');
Insert Into #data Values('7900059-1','5/1/07 8:09 AM','PB','3100');
Insert Into #data Values('7900059-1','5/1/07 8:16 AM','PL','3100');
Insert Into #data Values('7900059-1','5/1/07 8:53 AM','PB','3100');
Insert Into #data Values('7900059-1','5/1/07 9:02 AM','PL','3100');
Insert Into #data Values('7900059-1','5/1/07 10:04 AM','PB','3100');
Insert Into #data Values('7900059-1','5/1/07 10:16 AM','PL','3100');
Insert Into #data Values('7900059-1','5/1/07 12:20 PM','PB','3100');
Insert Into #data Values('7900059-1','5/1/07 12:37 PM','PL','3100');
Insert Into #data Values('7900059-1','5/1/07 1:59 PM','PB','3100');
Insert Into #data Values('7900059-1','5/1/07 2:13 PM','PL','3100');
Insert Into #data Values('7900059-1','5/2/07 6:49 AM','PB','3100');
Insert Into #data Values('7900059-1','5/2/07 6:59 AM','PL','3100');
Select * Into #PB From #Data Where [EventCode]='PB' ORDER BY 2
Select * Into #PL From #Data Where [EventCode]='PL' ORDER BY 2
Alter table #PB Add RowId int Identity(1,1);
Alter table #PL Add RowId int Identity(1,1);
Select
*
from
#PB PB
Join #PL PL
On
PB.RowId=PL.RowId
|||
I am using Sybase Embarcardero Rapid SQL 7.4
I think there is and identity function and I am trying to figure that out
|||Hi Manivannan.D.Sekaran,
Sure it could work. what about giving a try to:
create table #PB (
RowId int not null unique clustered,
...
)
create table #PL (
RowId int not null unique clustered,
...
)
insert into #PB (c1, ..., cn)
select c1, ..., cn
...
order by [EventDate]
insert into #PL (c1, ..., cn)
select c1, ..., cn
...
order by [EventDate]
...
AMB
Assign Sequential Numbers
I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.An IDENTITY column is just what you need. Check the Microsoft SQL Server CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp) documentation.
-PatP|||Hey...
SQLTeam still down?
USE Northwind
GO
CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
GO
INSERT INTO myTable99(Col2)
SELECT 'Brett' UNION ALL
SELECT 'Pat' UNION ALL
SELECT 'Gary'
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO|||I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||Originally posted by Brett Kaiser
Hey...
SQLTeam still down? Nah, at least I can see it from here.
-PatP
-PatP|||Hey...
SQLTeam still down?
USE Northwind
GO
CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
GO
INSERT INTO myTable99(Col2)
SELECT 'Brett' UNION ALL
SELECT 'Pat' UNION ALL
SELECT 'Gary'
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO|||I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||Originally posted by Brett Kaiser
Hey...
SQLTeam still down? Nah, at least I can see it from here.
-PatP
Subscribe to:
Posts (Atom)