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)
 Simple query?

Author  Topic 

EA
Starting Member

19 Posts

Posted - 2009-08-05 : 09:46:37
It seems to be a simple problem but it keeps giving horrible queries with subqueries and a lot of group by as the solution...

id nr text val1
1 1 aa A
2 1 bb A
3 2 cc B
4 3 aa C
5 3 ab D
6 4 cd E
7 4 rf F
8 4 rt F
9 4 qq G

How to get only the (complete) rows with the following conditions: nr stays the same but with different (and more than one) val1 value? (the result is the rows with id 4, 5, 6, 7 and 9).

Suggestions are very welcome!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 10:30:05
Here's one way:

declare @t table (id int, nr int, [text] varchar(2), val1 varchar(1))
insert @t
select 1, 1, 'aa', 'A' union all
select 2, 1, 'bb', 'A' union all
select 3, 2, 'cc', 'B' union all
select 4, 3, 'aa', 'C' union all
select 5, 3, 'ab', 'D' union all
select 6, 4, 'cd', 'E' union all
select 7, 4, 'rf', 'F' union all
select 8, 4, 'rt', 'F' union all
select 9, 4, 'qq', 'G'

select t.*
from (
select nr
from @t
group by nr
having count(distinct val1) > 1
and count(*) > 1
) d
join @t t
on t.nr = d.nr

OUTPUT:
id nr text val1
----------- ----------- ---- ----
4 3 aa C
5 3 ab D
6 4 cd E
7 4 rf F
8 4 rt F
9 4 qq G


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -