exclude status appear more than one in same ID

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

General SQL Server Forums

New to SQL Server Programming

exclude status appear more than one in same ID

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-16 : 23:16:28

how can i exclude those with more than one status? i tried using rows but it seems doesn't work.
ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close

the result should appear as:
ID status
2 new
2 close
4 new
4 close

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-17 : 01:51:06

declare @sample table (id int, status varchar(30))

insert @sample
select 1, 'new' union all
select 1, 'new' union all
select 1, 'close' union all
select 2, 'new' union all
select 2, 'close' union all
select 3, 'close' union all
select 3, 'close' union all
select 4, 'new' union all
select 4, 'close'

select * from @sample
where id not in (select id from @sample group by id,status having count(*) > 1)

Too old to Rock'n'Roll too young to die.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-17 : 02:02:31

thanks for the reply.

but i have a list of IDs.

i can't declare all right.

any other way?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-17 : 02:13:11

I have used your example data to give you a solution.
You only have to adapt this for your real environment.

select * from YourTableComesHere
where id not in (select id from YourTableComesHere group by id,status having count(*) > 1)

Too old to Rock'n'Roll too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-18 : 01:53:28

[code]SELECT ID,
[Status]
FROM (
SELECT ID,
[Status],
MAX(Items) OVER (PARTITION BY ID) AS Items
FROM (
SELECT ID,
[Status],
COUNT(*) OVER (PARTITION BY ID, [Status]) AS Items
FROM {YourTableNameHere}
) AS d
) AS d
WHERE Items = 1;[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources