Grouping Records & Assigning Sequential Number

I need to group records and assign a setid to the group. I have a
table with data that looks like this

ColA ColB
94015 01065
94016 01065
94015 01085
94015 01086
33383 00912
32601 00912

I need to create a resultset using just sql to look like this

ColA ColB GRP
94015 01065 1
94016 01065 1
94015 01085 1
94015 01086 1
33383 00912 2
32601 00912 2

The tricky part is resolving the many to many issue. A value in ColA
can belong to multiple values in ColB and a value in ColB can have
multiple values in ColA.Please explain the logic that determines GRP. What rule makes the first four
rows GRP=1 and the next two GRP=2 ?

--
David Portas
SQL Server MVP
--|||"cjm" <cjm136@.optonline.net> wrote in message news:62be3d63.0402120756.f08195b@.posting.google.co m...
> I need to group records and assign a setid to the group. I have a
> table with data that looks like this
> ColA ColB
> 94015 01065
> 94016 01065
> 94015 01085
> 94015 01086
> 33383 00912
> 32601 00912
> I need to create a resultset using just sql to look like this
> ColA ColB GRP
> 94015 01065 1
> 94016 01065 1
> 94015 01085 1
> 94015 01086 1
> 33383 00912 2
> 32601 00912 2
> The tricky part is resolving the many to many issue. A value in ColA
> can belong to multiple values in ColB and a value in ColB can have
> multiple values in ColA.

Not completely sure I understand your grouping criteria but hopefully
this is helpful.

CREATE TABLE T
(
colA VARCHAR(10) NOT NULL,
colB VARCHAR(10) NOT NULL,
PRIMARY KEY (colA, colB)
)

INSERT INTO T (colA, colB)
VALUES ('94015', '01065')
INSERT INTO T (colA, colB)
VALUES ('94016', '01065')
INSERT INTO T (colA, colB)
VALUES ('94015', '01085')
INSERT INTO T (colA, colB)
VALUES ('94015', '01086')
INSERT INTO T (colA, colB)
VALUES ('33383', '00912')
INSERT INTO T (colA, colB)
VALUES ('32601', '00912')

SELECT T.colA, T.colB, B.grp
FROM (SELECT B1.colB, COUNT(*) AS grp
FROM (SELECT colB
FROM (SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
GROUP BY colB) AS B1
INNER JOIN
(SELECT colB
FROM (SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
GROUP BY colB) AS B2
ON B2.colB <= B1.colB
GROUP BY B1.colB) AS B
INNER JOIN
(SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
ON A.colB = B.colB
INNER JOIN
T
ON T.colA = A.colA
ORDER BY B.grp, T.colB, T.colA

colA colB grp
32601 00912 1
33383 00912 1
94015 01065 2
94016 01065 2
94015 01085 2
94015 01086 2

Regards,
jag|||"John Gilson" <jag@.acm.org> wrote in message news:<W_7Xb.20407$Lp.1268@.twister.nyc.rr.com>...
> "cjm" <cjm136@.optonline.net> wrote in message news:62be3d63.0402120756.f08195b@.posting.google.co m...
Thanks JAG for the clever and clean solution!|||Is a given colB value allowed to belong to more than one group? If so then
John's solution looks good but I wasn't clear on this point from your sample
data.

Here's another solution that may or may not give the result you want (thanks
for the DDL and sample data John). I've added an extra row of sample data:

CREATE TABLE T
(
colA VARCHAR(10) NOT NULL,
colB VARCHAR(10) NOT NULL,
grp INTEGER NULL,
PRIMARY KEY (colA, colB)
)

INSERT INTO T (colA, colB)
VALUES ('94015', '01065')
INSERT INTO T (colA, colB)
VALUES ('94016', '01065')
INSERT INTO T (colA, colB)
VALUES ('94015', '01085')
INSERT INTO T (colA, colB)
VALUES ('94015', '01086')
INSERT INTO T (colA, colB)
VALUES ('33383', '00912')
INSERT INTO T (colA, colB)
VALUES ('32601', '00912')

INSERT INTO T (colA, colB)
VALUES ('32601', '01065')

John's query gives:

colA colB grp
---- ---- ----
32601 00912 1
33383 00912 1
32601 01065 1
94015 01065 2
94016 01065 2
94015 01085 2
94015 01086 2

Notice that 01065 appears in both groups. This iterative solution will put
all rows in the same group:

DECLARE @.grp INTEGER

UPDATE T
SET @.grp = grp = COALESCE(@.grp,0) + 1

WHILE @.@.ROWCOUNT>0
UPDATE T
SET grp =
(SELECT MIN(X.grp)
FROM T AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
WHERE EXISTS
(SELECT *
FROM T AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)

Note that the group numbers using this method are not "sequential" and may
have gaps but it's not clear from your original post exactly what the
sequence should be (if any).

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<jNadnasl3vRMQ7DdRVn-tw@.giganews.com>...
> Is a given colB value allowed to belong to more than one group? If so then
> John's solution looks good but I wasn't clear on this point from your sample
> data.
> Here's another solution that may or may not give the result you want (thanks
> for the DDL and sample data John). I've added an extra row of sample data:
> ...
There is no column called GRP in the table T and I don't want to alter
the table or create a temp table or otherwise UPDATE table T. How
would this be rewritten to return the result set as a query?

You made an important observation that a given colB value should
belong to only ONE group and I want to see the results of your code
with the record you added to the example. Sorry if this is a simple
conversion but I'm still learning.|||I'm not sure this is possible as a single query. It doesn't look like you
can avoid an iterative solution although you could turn it into a
table-valued function. I'm cross-posting to
microsoft.public.sqlserver.programming to see if anyone can come up with
better than this.

(http://groups.google.com/groups?sel...8195b%40posting.
google.com)

CREATE TABLE T(colA VARCHAR(10), colB VARCHAR(10) NOT NULL, PRIMARY KEY
(colA, colB))

INSERT INTO T (colA, colB) VALUES ('94015', '01065')
INSERT INTO T (colA, colB) VALUES ('94016', '01065')
INSERT INTO T (colA, colB) VALUES ('94015', '01085')
INSERT INTO T (colA, colB) VALUES ('94015', '01086')
INSERT INTO T (colA, colB) VALUES ('33383', '00912')
INSERT INTO T (colA, colB) VALUES ('32601', '00912')

/* Additional row makes it a single group: */
INSERT INTO T (colA, colB) VALUES ('32601', '01065')

GO

CREATE FUNCTION TGroupings ()
RETURNS @.t TABLE (colA VARCHAR(10) NOT NULL, colB VARCHAR(10) NOT NULL, grp
INTEGER NULL, PRIMARY KEY (colA,colB))

BEGIN
INSERT INTO @.t (colA, colB)
SELECT colA, colB
FROM T

DECLARE @.grp INTEGER

UPDATE @.t
SET @.grp = grp = COALESCE(@.grp,0) + 1

WHILE @.@.ROWCOUNT>0
UPDATE T
SET grp =
(SELECT MIN(X.grp)
FROM @.t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
FROM @.t AS T
WHERE EXISTS
(SELECT *
FROM @.t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)

UPDATE T
SET grp =
(SELECT COUNT(DISTINCT grp)
FROM @.t AS X
WHERE grp <= T.grp)
FROM @.t AS T

RETURN
END

GO

SELECT * FROM TGroupings()

This is the result with your original test-data:

colA colB grp
---- ---- ----
32601 00912 1
33383 00912 1
94015 01065 2
94015 01085 2
94015 01086 2
94016 01065 2

(6 row(s) affected)

And this is it with my extra row added:

colA colB grp
---- ---- ----
32601 00912 1
32601 01065 1
33383 00912 1
94015 01065 1
94015 01085 1
94015 01086 1
94016 01065 1

(7 row(s) affected)

--
David Portas
SQL Server MVP
--

"cjm" <cjm136@.optonline.net> wrote in message
news:62be3d63.0402201048.537be689@.posting.google.c om...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:<jNadnasl3vRMQ7DdRVn-tw@.giganews.com>...
> > Is a given colB value allowed to belong to more than one group? If so
then
> > John's solution looks good but I wasn't clear on this point from your
sample
> > data.
> > Here's another solution that may or may not give the result you want
(thanks
> > for the DDL and sample data John). I've added an extra row of sample
data:
> > ...
> There is no column called GRP in the table T and I don't want to alter
> the table or create a temp table or otherwise UPDATE table T. How
> would this be rewritten to return the result set as a query?
> You made an important observation that a given colB value should
> belong to only ONE group and I want to see the results of your code
> with the record you added to the example. Sorry if this is a simple
> conversion but I'm still learning.sql

Group-based authorization in Forms Authentication

Hi,
I am using the Forms Authentication Sample to authenticate users in RS. I am
trying to find a sample code to check setup groups and assign access on
Folders to groups instead of users.
Please help.
Cheers
SaiSearch the below URL for 'forms authentication groups'. I found quite a few
results:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs
--
Adrian M.
MCP
"Sai Vootla" <Sai@.discussions.microsoft.com> wrote in message
news:86332D5C-5A49-4F29-9ED0-B2ABBEDFBF6E@.microsoft.com...
> Hi,
> I am using the Forms Authentication Sample to authenticate users in RS. I
> am
> trying to find a sample code to check setup groups and assign access on
> Folders to groups instead of users.
> Please help.
> Cheers
> Saisql

group Number for millions of records.

I am using the following query which I found from a fragment of code
by itzik ben-gan to assign a common group id for group of records in my
case which have similar SSN and first Name and Last Name. if the SSN is
the same it should also check the first name and last name of the
record. Because records have more than three AKA names; I need to check
all the possibilities of first name last name combination to verify the
records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
Even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I
realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? Very
important issue.
2. While assigning group number, it doesn't give sequential numbers. It
skips some of the numbers( group Number 1,2 5,9...) I am just curious
about this(
not very important issue)
SELECT c1.fname, c1.lname, c1.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 grp_num
into tmp_FLS
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)
GO
distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.
CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO
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
sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333I am assuming that SSN means U.S. Social Security Number. If this is not
the case, ignore the rest of this post.
If SSN is the same you should be able to assume the records are in fact the
same people (this is the rule, but due to human error there have been
exceptions). For the rare exceptions identify them after the fact. Odds
are you won't come accross a true duplicate SSN, although if your data is
innaccurate it is more likely.
If you can go this route, simply assign the same group to anyone with the
same SSN, it will make it much simpler and much, much, much faster. If you
have to do it with all the name comparisons, at least remove the following
subquery and replace it with a different method for assigning a unique
GRP_NUM. That extra full table lookup for every row is what is causing your
performance problems.
(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.FNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
)
)
) AS GRP_NUM
<sql.greg@.gmail.com> wrote in message
news:1141742507.871093.35900@.i40g2000cwc.googlegroups.com...
> I am using the following query which I found from a fragment of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Because records have more than three AKA names; I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> Even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".
> 1. is there any other feasible and faster way to do this? Very
> important issue.
> 2. While assigning group number, it doesn't give sequential numbers. It
> skips some of the numbers( group Number 1,2 5,9...) I am just curious
> about this(
> not very important issue)
> SELECT c1.fname, c1.lname, c1.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 grp_num
> into tmp_FLS
> 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)
> GO
> distinct firstname, lastname and SSN table from the tu_people_data.
> I created this table to increase the query performance.
> CREATE TABLE [distFLS] (
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL
> ) ON [PRIMARY]
> GO
> 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
> sample data
> there is a column count_id after the tu_id and before fname(tu_id and
> count_id are primary keys)
> tu_id fname lname SSN
> 156078480 KRISINA WALSH 999999000
> 156078480 KRISTINA GIERER 999999000
> 156078480 KRISTINA WALSH 999999000
> 151257883 J SOTO 999999111
> 151257883 JOSE LARIOS 999999111
> 151257883 JOSE SOTO 999999111
> 151257883 L SOTO 999999111
> 136312525 ELADIO GARCIA 999999222
> 136312525 ELADIO NAVA 999999222
> 136312525 ELADIO NAVAGARCIA 999999222
> 136312525 GARCIA NAVA 999999222
> 149180940 DARREN SAUERWINE 999999333
> 149180940 DARREN SUAERWIN 999999333
>|||A while back, I ran a query against a customer account table, and found over
1000 unique SSN was used with a different date of birth. Those SSNs with the
highest frequency were obviously fake or just stubbs (ex: 111111111), but
there were some apparently valid numbers that were duplicated several
hundred times by different account holders.
"They were shocked to learn that 18 of the 19 terrorists possessed either
state-issued or counterfeit driver's licenses or ID cards and all 19 had
obtained Social Security numbers (SSNs) - some real, some fake."
http://www.cis.org/articles/2002/back1202.html
"Linda Trevino, who lives in a Chicago suburb, applied for a job last year
at a local Target department store, and was denied. The reason? She already
worked there -- or rather, her Social Security number already worked there."
http://www.msnbc.msn.com/id/6814673/
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23drgo7fQGHA.5552@.TK2MSFTNGP10.phx.gbl...
>I am assuming that SSN means U.S. Social Security Number. If this is not
> the case, ignore the rest of this post.
> If SSN is the same you should be able to assume the records are in fact
> the
> same people (this is the rule, but due to human error there have been
> exceptions). For the rare exceptions identify them after the fact. Odds
> are you won't come accross a true duplicate SSN, although if your data is
> innaccurate it is more likely.
> If you can go this route, simply assign the same group to anyone with the
> same SSN, it will make it much simpler and much, much, much faster. If
> you
> have to do it with all the name comparisons, at least remove the following
> subquery and replace it with a different method for assigning a unique
> GRP_NUM. That extra full table lookup for every row is what is causing
> your
> performance problems.
> (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.FNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
> OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
> OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
> )
> )
> ) AS GRP_NUM
>
> <sql.greg@.gmail.com> wrote in message
> news:1141742507.871093.35900@.i40g2000cwc.googlegroups.com...
>