sequential data selection 3

[northw]

Hi All,
how to identify different fields with in a group of records?

Example:

create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
union all
select 2, 'cd'
union all 
select 2, 'df'

select * from #test

I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.

Thanks in advance.

 

[TamarGranor]

What if there are more than two records for an ID. Do you want 'Matched' if there's any match or only if all match?

Tamar

 

[northw]

Tamar,
There can be more than two records, same thing applies if all the records with same ID matched then it has to be matched or else unmatched.

Thanks!

 

[northw]

Updated ddl

create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
union all
select 2, 'cd'
union all 
select 2, 'df'
union all
select 3, 'ab'
union all
select 3, 'cd'
union all 
select 3, 'df'
union all
select 4, 'xy'
union all
select 4, 'xy'
union all 
select 4, 'xy'


select * from #test

 

[yelworcm]

with txtcount (ID, valuecount)
as
(select ID, count(distinct text)
 from #test
 group by ID)

select a.ID, a.[text],
	case when t.valuecount = 1 then 'matched' else 'unmatched' end
from txtcount t join
	#test a on t.ID = a.ID

 

[Olaf Doschke]

First just declare a table variable, you don't have to drop and recreate it in case it exists or not.
Then my solution is as follows: First select all distinct records. Afterwards group by id, if some ID only has equal records it will occur just once in the distinct records, if there are different records for the same id, you have a count of 2 or more for that ID. A case can then evaluate the group (ID) as 'match' or 'diff', which you can also put as 'true' or 'false', 0 or 1 or whatever you like:

Declare @test as Table (ID int, Text varchar(10))

insert into @test
select 1, 'ab'
union all
select 1, 'ab'
union all
select 2, 'cd'
union all 
select 2, 'df'
union all
select 3, 'ab'
union all
select 3, 'cd'
union all 
select 3, 'df'
union all
select 4, 'xy'
union all
select 4, 'xy'
union all 
select 4, 'xy'

;with distinctrecords as
(select distinct * from @test)

select id, case when count(*)=1 then 'match' else 'diff' end as result  From distinctrecords group by id

Result:
[pre]1 match
2 diff
3 diff
4 match[/pre]

Bye, Olaf.

 

[Olaf Doschke]

Count(distinct field) is a good idea.

If my final result (ID, match or diff) is sufficient for you, the solutions combined would simplify to:

Declare @test as Table (ID int, Text varchar(10))

insert into @test
values (1, 'ab')
,      (1, 'ab')
,      (2, 'cd')
,      (2, 'df')
,      (3, 'ab')
,      (3, 'cd')
,      (3, 'df')
,      (4, 'xy')
,      (4, 'xy')
,      (4, 'xy')

select ID, case when count(distinct text)=1 then 'matched' else 'unmatched' end as result 
 from @test
 group by ID

 

[yelworcm]

Thanks, Olaf. Although I have to admit, I have not tested for what effects null values may have. I think they may give a false "matched" result.

 

[gk53]

use

Declare @test as Table (ID int, Text varchar(10))

insert into @test
values (1, 'ab')
,      (1, 'ab')
,      (2, 'cd')
,      (2, 'df')
,      (3, 'ab')
,      (3, 'cd')
,      (3, 'df')
,      (4, 'xy')
,      (4, 'xy')
,      (4, 'xy')

select ID, case when count(distinct isnull(text,''))=1  then 'matched' else 'unmatched' end as result 
 from @test
 group by ID

it will handle nulls

 

[Olaf Doschke]

You can easily add NULL values to your test data and see. They won't be counted with count(distinct text), but they will, if using gk53's ISNULL solution.

Bye, Olaf.

 

[gk53]

I think null value create a duplicate record...

 

[Olaf Doschke]

I don't know what you mean, gk53.

It's clear count(distinct field) does not count occurrances of NULL, but count(distinct isnull(field,'')) does count these records, too. The only problem is, if you have empty and null values in the same group it counts as a match. Still your solution is fine for most scenarios.

I was merely suggesting to yelworcm how he could have tested the impact or non impact of NULLs.

Bye, Olaf.