Comapring groups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Comapring groups

Author

Topic

CanadaDBA

583 Posts

Posted - 2007-01-09 : 09:01:12

Assume the following table:

I am writing a Function or SP to return 0 if each group in column A has the same values for in B.

For example, if 101 has 2000 and 2001, then 102 has 2000 and 2001, … then it returns 0 which means all the groups in column A has both values of 2000 and 2001.

The function/SP will return 1 for the above table because 104 has only one value of 2001. If it had a value of 2000, then the function would return 0.

It is possible to implement the work with a cursor but it is costly. Any better idea?

Thanks,

Canada DBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-09 : 09:21:58

is the PK of this table a combination of columns A and B ? If not, then you need to tell us what it is.

If it IS (A/B), then something like this should work:


select case when Min(Matches) = Max(matches) then 0 else 1 end
from
(
  select B, count(*) as Matches
  from YourData
  group by B
) x

That says: Group all the data by column B, and return the # of matches for each value in column A. If, across the board, the number of matches is consistent for all values of B, then return 0 else return 1.

There may be a flaw in the logic, but I think it should work.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 09:27:20

Jeff, consider this test data

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-09 : 09:27:29

My attempt:

if exists (select b, count(*) from @t group by b having count(*)%2 = 1)
	select 1
else
	select 0

Although this has to be changed if you add more years in the check.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 09:34:20

[code]-- prepare sample data
declare @t table (a int, b int)

insert @t
select 103, 2000 union all
select 103, 2001 union all
select 104, 2000 union all
select 105, 2001

-- Jeff
select case when Min(Matches) = Max(matches) then 0 else 1 end
from
(
select B, count(*) as Matches
from @t
group by B
) x

-- Peso
select sign(sum(t))
from (
select 0 as t
union all
select count(*)
from @t
group by a
having count(*) <> 2
or min(b) <> 2000
or max(b) <> 2001
) as d

-- Harsh
if exists (select b, count(*) from @t group by b having count(*)%2 = 1)
select 1
else
select 0[/code]The COUNT(*) <> 2 can be omitted if there is no chance of duplicate values, such as two records with a=100 and b=2000

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-09 : 10:02:30

Ah, I think I see the flaw in my alogorithm now, thanks Peso, I knew there was one there somewhere....

Also, remember, we don't know yet if there are only going to be values of 2001, 2000 in the data, or if they'll only ever be 2 values per group -- there may be more.

- Jeff

CanadaDBA

583 Posts

Posted - 2007-01-09 : 10:10:45

Wonderful! How different you are looking to the problem and solved it! I feel I am getting old :(

The years can go as many as possible, i.e. 2000, 2001, 2002, 2003, ...

The groups can be 101, 102, 103, 104, ...

The There is a PK on A and B. So, the rows are unique.

Thank You!

Canada DBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-09 : 10:14:13

ok, here's my next attempt:


select case when min(N) = (select count(distinct a) from @t) then 0 else 1 end
from
(
select b, count(*) as N
from @t 
group by b
) x

which says: for each b, get the # of a's. If the minimum number of a's per b is also the total number of a's, then they all should match up.

I *think* that should work just fine, but we'll see ...

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 10:32:48

This might also work

SELECT		SIGN(COUNT(*))
FROM		@t AS t1
CROSS JOIN	@t AS t2
LEFT JOIN	@t AS t ON t.a = t1.a AND t.b = t2.b
WHERE		t.a IS NULL

Peter Larsson
Helsingborg, Sweden

CanadaDBA

583 Posts

Posted - 2007-01-09 : 10:33:24

Jeff, that's smart! :)

Thanks everybody!

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-01-09 : 10:42:47

Peso, I tried for several different senarios and it works! :)

Thanks!

quote:
Originally posted by Peso

This might also work
SELECT SIGN(COUNT(*))
FROM @t AS t1
CROSS JOIN @t AS t2
LEFT JOIN @t AS t ON t.a = t1.a AND t.b = t2.b
WHERE t.a IS NULL

Peter Larsson
Helsingborg, Sweden

Canada DBA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-09 : 10:52:34

Peter:

Your first approach fails when it comes to three years but second works.

How about this Jeff's modified query:

if exists( select a from @t group by a having min(b) = max(b)) and (select count(*) % count(distinct a) from @t)  <> 0
 select 1
else
 select 0

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 10:52:47

I think Jeff's suggestion works too and have MUCH better performance than mine!

Peter Larsson
Helsingborg, Sweden

CanadaDBA

583 Posts

Posted - 2007-01-09 : 11:42:38

That's right! The cross join on a huge table looks costly. But the solution is tricky though :)

quote:
Originally posted by Peso

I think Jeff's suggestion works too and have MUCH better performance than mine!

Peter Larsson
Helsingborg, Sweden

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-01-09 : 11:56:01

It doesn't work for:

declare @MyTable table(A int,B int )
insert into @MyTable
select 101 ,2000 UNION ALL
select 101 ,2001 UNION ALL
select 101 ,2002 UNION ALL
select 102 ,2000 UNION ALL
select 102 ,2001 UNION ALL
select 102 ,2002 UNION ALL
select 103 ,2000 UNION ALL
select 103 ,2001 UNION ALL
select 103 ,2002 UNION ALL
select 104 ,2001 UNION ALL
select 104 ,2000

quote:
Originally posted by harsh_athalye

Peter:

Your first approach fails when it comes to three years but second works.

How about this Jeff's modified query:

if exists( select a from @t group by a having min(b) = max(b)) and (select count(*) % count(distinct a) from @t) <> 0
select 1
else
select 0

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Canada DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources