Select upon certain condition - 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)

Select upon certain condition

Author

Topic

matrixrep
Starting Member

30 Posts

Posted - 2011-07-14 : 10:04:02

Hi all,

I have the following table:

Item Type Kind
---- ---- ----
1 a P
1 b P
1 a M
2 c P
2 d P
2 c M
2 d M
3 e T
3 f P

On column Item,
For 1, when the number of P is not equal the number of M in the column Kind , it is okay.

For 2, when the number of P is equal the number of M in the column Kind, i want item 2 out.

For 3, since there is a kind T, i want item 3 out,

So at the end i will have,

Item Type Kind
---- ---- ----
1 a P
1 b P
1 a M

I have thousand of rows like that.
Any suggestion would be deeply appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-14 : 11:02:03

Try this

select item from table
group by item
having sum(case when kind in ('M','P') then 1 else -100 end)%2=1

Madhivanan

Failing to plan is Planning to fail