distinct over empty - 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 2008 Forums

Transact-SQL (2008)

distinct over empty

Author

Topic

nimda
Starting Member

4 Posts

Posted - 2012-04-10 : 12:25:09

I have the following table with 3 rows and columns A_ID, B_ID and C_ID. As you can see B_ID is empty in the first row, B_ID and C_ID are empty in the second row and C_ID is empty in de last row:

A_ID,B_ID,C_ID
14020096,,889069
14020096,,
14020096,868977,

I am looking for a way to get this result:
A_ID,B_ID,C_ID
14020096, 868977, 889069

Is there any?

Thanks in advance!

Johan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-04-10 : 13:22:22

you could:
select max(a_id) as a_id, max(b_id) as b_id, max(c_id) as c_id

Be One with the Optimizer
TG

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-10 : 13:28:29

[code]
declare @nimda table(A_ID int, B_ID int, C_ID int)
insert into @nimda
select 14020096, null , 889069
UNION
SELECT 14020096, null , null
UNION
SELECT 14020096, 868977 , null
UNION
select 14020097, null , 889069
UNION
SELECT 14020097, null , null
UNION
SELECT 14020097, 868977 , null

SELECT * FROM
(
select DISTINCT COALESCE(a.A_ID, b.A_ID) as A_ID,
COALESCE(a.B_ID, b.B_ID) as B_ID,
COALESCE(a.C_ID, b.C_ID) as C_ID
from @nimda a
inner join @nimda b
on a.A_ID = b.A_ID
)
a
where A_ID IS NOT NULl
AND B_ID IS NOT NULL
AND C_ID IS NOT NULL
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:38:04

what if there are multiple non blank values for B_ID and C_ID within same A_ID group? in that case, whats the rule for selecting one among them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-11 : 05:21:51

Please post a little more sample data to tell us the logic a bit more clearly.

Vinu Vijayan

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-11 : 05:22:51

With the given logic and the given data....I think the solution IG gave will be good

nimda
Starting Member

4 Posts

Posted - 2012-04-13 : 07:34:03

Thanks for your help IG and others! I didn't think of such a simple yet elegant solution.
In reply to visakh: that situation will never exist (fortunately).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-13 : 08:15:43

If a_id always exist,

select a_id, max(b_id) as b_id, max(c_id) as c_id from dbo.table1 group by a_id

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:49:23

I feel Pesos solutions is what you be after. the solutions using just max() will return only a single row which I'm not sure is what OP is looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources