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.. :)