select all group having same value

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

select all group having same value

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2012-01-05 : 05:32:38

Hello

I have a table with 3 column
BoxID
Date
Checked

Many rows can have the same BoxID
Checked can have value 0 o 1
I need to sellect All distinct BoxID having Date NOT NULL AND
WITH ALL Checked value = 1

What will be the best way to do that ?

Thanks for any help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-05 : 05:57:27

select distinct BoxID
from MyTable t1
where Date is not null
and Checked = 1
and not exists(select * from MyTable t2 where t2.BoxID = t1.BoxID and checked = 0)

No, you're never too old to Yak'n'Roll if you're too young to die.

olibara
Yak Posting Veteran

94 Posts

Posted - 2012-01-05 : 06:55:02

Thank you SQLFred

Yes your solution works but I'm not sure it is very efficient
I had the Idea to use a PARTITION OVER but I'm not sure about it either

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-05 : 07:05:10

[code]
select BoxID
from MyTable
where date is not null
group by BoxID
having min(checked) = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-05 : 07:14:28

quote:
Originally posted by khtan

select BoxID
from MyTable
where date is not null
group by BoxID
having min(checked) = 1

KH
[spoiler]Time is always against us[/spoiler]

That's fine

No, you're never too old to Yak'n'Roll if you're too young to die.

olibara
Yak Posting Veteran

94 Posts

Posted - 2012-01-05 : 09:11:11

yes nice
thank you !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources