Select / group by - 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

Select / group by

Author

Topic

krissyM2
Starting Member

4 Posts

Posted - 2009-12-28 : 06:05:04

Hi,

I want to select from one column a certain value that occurrs after I grouped another column.

See this table:

GroupID Week Day Date AAAA ZZZZ DDD
14 52 5 NULL NULL NULL FALSE
14 52 6 NULL NULL NULL FALSE
14 52 7 NULL NULL NULL FALSE
14 53 1 NULL NULL NULL FALSE
14 53 6 NULL NULL NULL FALSE
14 53 7 NULL NULL NULL FALSE
16 0 1 NULL 1 NULL TRUE
16 0 2 NULL 1 NULL TRUE
16 0 3 NULL 1 NULL TRUE
16 0 4 NULL 1 NULL TRUE
16 0 5 NULL NULL NULL FALSE
16 0 6 NULL NULL NULL FALSE
16 53 4 NULL NULL NULL FALSE
16 53 5 NULL NULL NULL FALSE
16 53 6 NULL NULL NULL FALSE
16 53 7 NULL NULL NULL FALSE
17 0 1 NULL NULL NULL FALSE
17 52 3 NULL NULL NULL FALSE
17 52 4 NULL NULL NULL FALSE
17 52 5 NULL NULL NULL FALSE

I want to select the GroupID's that does not have a value for Week = 53. I.e. in this case the result would only display GroupID: 17.

Should be very easy, right?

Thanks.

/Kriss

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-28 : 06:46:28

HI,

select distinct groupid from @tab as t
where groupid not in
( select distinct groupid from @tab as t where groupid = t.groupid and week = 53 )

Thanks,
vikky.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 07:29:06

SELECT GroupID FROM your_table
GROUP BY GroupID
HAVING MAX(CASE WHEN [week]=53 then 1 else 0 END)=0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources