|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how do I search for duplicate values in normalized data ?
but my query just returns everything. What am I missing? Thanks alot!! create table test (testset int , value int) insert into test values (1,1) insert into test values (1,2) insert into test values (1,3) insert into test values (2,1) insert into test values (2,2) insert into test values (2,3) insert into test values (3,1) insert into test values (3,2) insert into test values (3,4) select a.testset from test a, test b where a.value = b.value group by a.testset drop table test Hmm...I just threw your script into QA and I get:
testset ----------- 1 2 3 (3 row(s) affected) I assume that is what you would have expected to get? Show quoteHide quote "D" wrote: > I'm trying to search out the first 2 testsets because ALL the values repeat > but my query just returns everything. What am I missing? > > Thanks alot!! > > > > create table test (testset int , value int) > > insert into test values (1,1) > insert into test values (1,2) > insert into test values (1,3) > insert into test values (2,1) > insert into test values (2,2) > insert into test values (2,3) > insert into test values (3,1) > insert into test values (3,2) > insert into test values (3,4) > > > select a.testset from test a, test b where a.value = b.value group by > a.testset > > drop table test > > > Actually I only wanted 1 and 2 back because testset 3 doesn't fully match 1
and 2. I want to see all the testsets whose values match exactly thanks Show quoteHide quote "Donna Lambert" <DonnaLamb***@discussions.microsoft.com> wrote in message news:4ECD1E09-B608-4A15-ACBE-62C8C9E0397F@microsoft.com... > Hmm...I just threw your script into QA and I get: > > testset > ----------- > 1 > 2 > 3 > > (3 row(s) affected) > > I assume that is what you would have expected to get? > > > "D" wrote: > >> I'm trying to search out the first 2 testsets because ALL the values >> repeat >> but my query just returns everything. What am I missing? >> >> Thanks alot!! >> >> >> >> create table test (testset int , value int) >> >> insert into test values (1,1) >> insert into test values (1,2) >> insert into test values (1,3) >> insert into test values (2,1) >> insert into test values (2,2) >> insert into test values (2,3) >> insert into test values (3,1) >> insert into test values (3,2) >> insert into test values (3,4) >> >> >> select a.testset from test a, test b where a.value = b.value group by >> a.testset >> >> drop table test >> >> >> D wrote:
> Actually I only wanted 1 and 2 back because testset 3 doesn't fully Unless I'm missing something here, you would first need to know what > match 1 and 2. I want to see all the testsets whose values match > exactly testset qualifies as the comparator. For example, if you assume that testset 1 is the set you want to use as a comparison against other testsets, you could use something like the following. It uses the CHECKSUM_AGG() function, which if you search older posts does not produce a unique checksum with some "unlikely" data values, but it may suffice in this case. There is probably a more elegant set-based solution here (others in the ng may provide one): select CHECKSUM_AGG(value) as "TEST CHECKSUM", testset from test Group By testset Having CHECKSUM_AGG(value) = ( Select CHECKSUM_AGG(value) From test Where testset = 1 ) TEST CHECKSUM testset ------------- ----------- 0 1 0 2 thanks for the info.
I had posted a similiar question earlier when I was searching for smaller patterns within the testsets that Hugo answered for me very nicely. His solution was to create a table of the combinations and match that up to the test data. The problem I had was when I wanted to match an entire set of 5 or more numbers it took a really long time. It seems like a simple thing to search for matching patterns of testsets but it doesn't seem so. Thanks Show quoteHide quote "David Gugick" <davidg-nospam@imceda.com> wrote in message news:%23gy48SLVFHA.616@TK2MSFTNGP12.phx.gbl... >D wrote: >> Actually I only wanted 1 and 2 back because testset 3 doesn't fully >> match 1 and 2. I want to see all the testsets whose values match >> exactly > > Unless I'm missing something here, you would first need to know what > testset qualifies as the comparator. For example, if you assume that > testset 1 is the set you want to use as a comparison against other > testsets, you could use something like the following. It uses the > CHECKSUM_AGG() function, which if you search older posts does not produce > a unique checksum with some "unlikely" data values, but it may suffice in > this case. There is probably a more elegant set-based solution here > (others in the ng may provide one): > > select CHECKSUM_AGG(value) as "TEST CHECKSUM", testset > from test > Group By testset > Having CHECKSUM_AGG(value) = ( > Select CHECKSUM_AGG(value) > From test > Where testset = 1 ) > > TEST CHECKSUM testset > ------------- ----------- > 0 1 > 0 2 > > > > > > > > > > > > -- > David Gugick > Imceda Software > www.imceda.com  
Other interesting topics
|
|||||||||||||||||||||||
