Picking the specific data - 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

General SQL Server Forums

New to SQL Server Programming

Picking the specific data

Author

Topic

callinnn
Starting Member

7 Posts

Posted - 2007-05-16 : 09:20:12

I have in my table something like this
Col1 Col2
6 O
6 O
6 C
6 C
6 C
5 O
5 O

i want the value as 6 iff all the corresponding
records in col2 are C
similarly, since for 5 there are no C it should
not pick record 5

Please suggest me a query for this

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-16 : 09:51:23

Wow, what do you want? I see the table and the sample data, but not expected outcome.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

yumyum113
Starting Member

31 Posts

Posted - 2007-05-16 : 09:56:42

something like this?

declare @temptable table
(
col1 int,
col2 varchar(1)
)

insert into @temptable
select 6,'O' union all
select 6,'O' union all
select 6,'C' union all
select 6,'C' union all
select 6,'C' union all
select 5,'O' union all
select 5,'O' union all
select 4,'C' union all
select 4,'C'

select t1.col1
from @temptable t1
left join
(select col1,count(col1) 'col1_count' from @temptable group by col1) t2 on t1.col1 = t2.col1
where t1.col2 = 'C'
group by t1.col1,t1.col2,t2.col1_count
having count(t1.col2) = t2.col1_count

callinnn
Starting Member

7 Posts

Posted - 2007-05-16 : 09:57:03

I have in my table something like this
Col1 Col2
6 O
6 O
6 C
6 C
6 C
5 O
5 O
3 C
3 C

i want the value as 6 iff all the corresponding
records in col2 are C
similarly, since for 5 there are no C it should
not pick record 5

Please suggest me a query for this

*i want to select col1 where all col2='C'
*It should give me an output as
*Col1
3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 10:20:30

[code]
select c.col1
from
(
select col1, cnt = count(*)
from tbl
where col2 = 'C'
group by col1
) c
inner join
(
select col1, cnt = count(*)
from tbl
group by col1
) a
on c.col1 = a.col1
where c.cnt = a.cnt
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-16 : 10:30:41

[code]declare @t table
(
a int, b varchar(1)
)

insert @t
select 6, 'O' union all
select 6, 'O' union all
select 6, 'C' union all
select 6, 'C' union all
select 6, 'C' union all
select 5, 'O' union all
select 5, 'O' union all
select 3, 'C' union all
select 3, 'C'

select a
from @t
group by a
having min(b) = max(b) and min(b) = 'C'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 11:42:30

If only O and C are available as option value,

select a
from @t
group by a
having max(b) = 'C'

or

select a
from @t
group by a
having min(b) = 'C' and min(b) = 'C'

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-16 : 11:45:04

quote:
Originally posted by Peso

If only O and C are available as option value,

select a
from @t
group by a
having max(b) = 'C'

or

select a
from @t
group by a
having min(b) = 'C' and ~~min~~max(b) = 'C'

Peter Larsson
Helsingborg, Sweden

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 11:51:03

Thanks.

Peter Larsson
Helsingborg, Sweden

callinnn
Starting Member

7 Posts

Posted - 2007-05-17 : 09:57:41

Thank you for giving me such a nice detailed solution

I m very thankful to u since i got it in half an hour of
my posting and since it was really urgent

I don't have words to really than but it is as many times
as this number

9999999999999999999999999999999999999999999999999999999+1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 10:42:34

<<
I don't have words to really than but it is as many times
as this number

9999999999999999999999999999999999999999999999999999999+1
>>

Better say "Infinity"

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources