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

SQL Server 2000 Forums

Transact-SQL (2000)

group by

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-12-13 : 16:02:38

I want to do a group by id and retrieve the records where atleast one of the records returned by that group has null value in last name.

Id Last Name
1 paul
1 null
1 mike
2 carol
2 jen

Expected Result:
1 paul
1 null
1 mike.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 16:05:32

Something like this should work:

SELECT Id, Name
FROM YourTable t1
WHERE EXISTS (SELECT * FROM YourTable t2 WHERE t1.Id = t2.Id AND t2.Name IS NULL)
GROUP BY Id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 19:28:41

[code]SELECT Id, max(Name)
FROM YourTable t1
WHERE EXISTS (SELECT * FROM YourTable t2 WHERE t1.Id = t2.Id AND t2.Name IS NULL)
GROUP BY Id[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 03:44:40

quote:
Originally posted by khtan

SELECT Id, max(Name)
FROM YourTable t1
WHERE EXISTS (SELECT * FROM YourTable t2 WHERE t1.Id = t2.Id AND t2.Name IS NULL)
GROUP BY Id

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

SELECT Id, Name
FROM YourTable t1
WHERE EXISTS (SELECT * FROM YourTable t2 WHERE t1.Id = t2.Id AND t2.Name IS NULL)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources