sql problem - 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)

sql problem

Author

Topic

salilkol
Starting Member

2 Posts

Posted - 2009-12-04 : 03:03:12

Hi i have following table (very big table)

columns
ID , CODE ,...............................

values

1 , X , ..........
1, X ,...........
2, A,............
2, A,............
3, A,............
3, A,............
4, C,............
4, D,............
.
.
.

i need to find out all rows which are having more than one value for each ID
example 4 is having C and D

can any one help

Thanks
Salil

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-04 : 03:21:57

SELECT *
FROM Tablename
WHERE id NOT IN(SELECT id
FROM Tableaname
GROUP BY id, code
HAVING COUNT(id) > 1)
Kindly reply after trying this..

Balaji.K

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-04 : 03:24:52

Hi

SELECT ID, COUNT(*) FROM <Table_Name> GROUP BY ID
HAVING COUNT(*) > 1

-------------------------
R...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-04 : 03:38:06

try like this


declare @t table (id int, val varchar(2))
insert into @t 
select 
3 , 'A' union all select 
3, 'A' union all select 
4, 'C' union all select 
5, 'C' union all select 
4, 'D'
select t.* FROM 
@t t
inner join (select id,val,count(*) as cnt from @t group by id,val)s on s.id = t.id and s.val = t.val
inner join (select id,count(*) as icnt from @t group by id)si on si.id = t.id 
where icnt > 1 and cnt = 1

salilkol
Starting Member

2 Posts

Posted - 2009-12-04 : 03:53:44

Hi thanks

kbhere your query will work but as table is too big if i create 2 joines performance will effect

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-04 : 04:16:12

Hi salilkol

The outer and inner queries are independent each other.This executes inner query for each and every rows in outer query.It is better to use joins instead of sub query.

-------------------------
R...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-04 : 07:34:43

Something else to try...

declare @t table (id int, code char(1))
insert @t 
          select 1, 'X'
union all select 1, 'X'
union all select 2, 'A'
union all select 2, 'A'
union all select 3, 'A'
union all select 3, 'A'
union all select 4, 'C'
union all select 4, 'D'

select id from @t group by id having count(distinct code) > 1

select * from @t a where exists (select * from @t where id = a.id and code != a.code)

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 07:44:06

Yet another way

select id from @t
group by id
having min(code)<>max(code)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources