Select distinct not-null rows SQL server 2005

I ran into the following problem.

I have a table like this:

ID   ID1     ID2     ID3     ID4     ID5
1   NULL    NULL    NULL    NULL    1
2   NULL    NULL    NULL    2       NULL
3   NULL    NULL    NULL    2       1
4   3       NULL    NULL    2       NULL
5   3       NULL    NULL    2       1
6   NULL    5       NULL    2       NULL

And I need to get distinct rows it terms that NULL equals any value. For this example the answer is:

ID   ID1     ID2     ID3     ID4     ID5
5   3       NULL    NULL    2       1
6   NULL    5       NULL    开发者_StackOverflow社区2       NULL

P.S. Here ID is primary key hence unique. ID1-ID5 - any integers.

Thanks in advance! UPDATED

Saying that null equals any number I mean that it's absorbed by any number.

This works, don't know if it can be made any simpler

SELECT ID1, ID2, ID3, ID4, ID5
FROM IDS OUTT
WHERE NOT EXISTS (SELECT 1
                FROM IDS INN
                WHERE OUTT.ID != INN.ID AND
                      (ISNULL(OUTT.ID1, INN.ID1) = INN.ID1 OR (INN.ID1 IS NULL AND OUTT.ID1 IS NULL)) AND
                      (ISNULL(OUTT.ID2, INN.ID2) = INN.ID2 OR (INN.ID2 IS NULL AND OUTT.ID2 IS NULL)) AND
                      (ISNULL(OUTT.ID3, INN.ID3) = INN.ID3 OR (INN.ID3 IS NULL AND OUTT.ID3 IS NULL)) AND
                      (ISNULL(OUTT.ID4, INN.ID4) = INN.ID4 OR (INN.ID4 IS NULL AND OUTT.ID4 IS NULL)) AND
                      (ISNULL(OUTT.ID5, INN.ID5) = INN.ID5 OR (INN.ID5 IS NULL AND OUTT.ID5 IS NULL)))

EDIT: Found a sweeter alternative, if your ids never have negative numbers

SELECT ID1, ID2, ID3, ID4, ID5
FROM IDS OUTT
WHERE NOT EXISTS (SELECT 1
                FROM IDS INN
                WHERE OUTT.ID != INN.ID AND
                      coalesce(OUTT.ID1, INN.ID1,-1) = isnull(INN.ID1,-1) AND
                      coalesce(OUTT.ID2, INN.ID2,-1) = isnull(INN.ID2,-1) AND
                      coalesce(OUTT.ID3, INN.ID3,-1) = isnull(INN.ID3,-1) AND
                      coalesce(OUTT.ID4, INN.ID4,-1) = isnull(INN.ID4,-1) AND
                      coalesce(OUTT.ID5, INN.ID5,-1) = isnull(INN.ID5,-1))  

EDIT2: There is one case where it won't work - in case two rows (with different ids) have exact same form. I am assuming that it is not there. If such a thing is present, then first create a view with a select distinct on the base table first, and then apply this query.

Statement of your problem as I understand it:

You start with the full table:

ID   ID1     ID2     ID3     ID4     ID5
1   NULL    NULL    NULL    NULL    1
2   NULL    NULL    NULL    2       NULL
3   NULL    NULL    NULL    2       1
4   3       NULL    NULL    2       NULL
5   3       NULL    NULL    2       1
6   NULL    5       NULL    2       NULL

Then you eliminate "duplicate" rows, ie. rows that have less, but the same values as other rows (except NULL — and the ID column is not included):

• Row 1 is eliminated because row 3 is identical, but has more values in the places where row 1 has NULL.

• Row 2 likewise gets eliminated by (either of) row 2 or 4.

• Row 3 and 4 are eliminated by row 5.

You're then left with rows 5 and 6:

ID   ID1     ID2     ID3     ID4     ID5
5   3       NULL    NULL    2       1
6   NULL    5       NULL    2       NULL

My answer:

Frankly, I don't see how this could be done with SQL's SELECT DISTINCT, or more generally, with SQL's set-based logic. I could imagine that you might be able to do this kind of filtering with a more procedural approach (e.g. with cursors) — but I can't provide a solution for this.

A note about terminology:

NULL equals any value

NULL never equals any value, because NULL is itself not a value; it is the absence of a value. NULL essentially means "unknown". (The fact that NULL is not a value is the reason why you shouldn't write IDx = NULL, but IDx IS NULL instead.)

If ID1, ID2 (...) has always the same value, as in your example, you could do it

Select 
 SUM(id1)/COUNT(id1),
 SUM(id2)/COUNT(id2),
 SUM(id3)/COUNT(id3),
 SUM(id4)/COUNT(id4),
 SUM(id5)/COUNT(id5)  From TABLE

The functions SUM and COUNT will ignore that null values. But still little confused your question.. :)