This is a Stumper.....for me at least

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

This is a Stumper.....for me at least

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2008-01-18 : 12:41:40

I have the following result set:


KeyID   Linked_id   
678	NULL
678	1
679	NULL
679	1
680	NULL
680	1
681	NULL
681	1
682	NULL
682	1
683	NULL
684	NULL
685	NULL
686	NULL
687	NULL

My business rules:

In a single select statement I want to:

1. Bring back 1 of each keyID
2. If two of the same keyId exist in the results, and one of the LinkedID's has a value and the other is null, I want to use the linkedID that has a value.

I've tried an inner join on itself without success. Any thoughts?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-18 : 12:47:13

Do a group by on KeyId and a MIN(Linked_id) and see if you get the results you are looking for..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 12:56:08

[code]SELECT t.KeyID,
MAX(t.Linked_id) AS Linked_id
FROM
(SELECT KeyID,
ISNULL(Linked_id,0) AS Linked_id
FROM Table)t
GROUP BY t.KeyID[/code]

label
Posting Yak Master

197 Posts

Posted - 2008-01-18 : 13:14:51

quote:

Originally posted by visakh16

SELECT t.KeyID,
MAX(t.Linked_id) AS Linked_id
FROM
(SELECT KeyID,
ISNULL(Linked_id,0) AS  Linked_id 
FROM Table)t
GROUP BY t.KeyID

Thanks, that worked perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources