Finding values with the multiple status

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

Finding values with the multiple status

Author

Topic

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-11-01 : 23:02:19

Hi Team,

I need to find the Partner who is part of Candidate and Verified status
in the below table

Partner Status
a Candidate
a Verified
c Candidate
b Candidate
b Verified
d Candidate
e Candidate

I have tried

Select Partner from tablename where status='Candidate' and 'Verified'. But It is not giving any partners... But the result should be

Partners
A
B

Please help me.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-11-01 : 23:24:50

[code]
declare @a table(col1 varchar(1), col2 varchar(20))insert into @a values
('a', 'Candidate'),
('a', 'Verified'),
('c', 'Candidate'),
('b', 'Candidate'),
('b', 'Verified'),
('d', 'Candidate'),
('e', 'Candidate')

select col1
from (
select col1
, Candidate = SUM(case when col2 = 'Candidate' then 1 else 0 end)
, Verified = SUM(case when col2 = 'Verified' then 1 else 0 end)
from @a
group by col1)src
where Candidate > 0 and Verified > 0
[/code]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-11-01 : 23:45:13

[code]
select col1
from (
select col1, rn = ROW_NUMBER() over (partition by col1 order by col2)
from @a
)src
where rn = 2
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources