Select distinct multiple field without NULL

Question

I have a table with Value ID and Value

--------------
| id | value |
--------------
|  1 |  NULL |
--------------
|  1 |     A |
--------------
|  2 |  NULL |
--------------
|  2 |  NULL |
--------------
|  3 |     B |
--------------
|  3 |     B |
--------------
|  3 |     B |
--------------

I need to select distinct id and corresponding value from the table. When selecting the Id should be unique and if it is having multiple values in the value field it should retrieve only not NULL values

So the result should be like below.

--------------
| id | value |    
--------------
|  1 |     A |
--------------
|  2 |  NULL |
--------------    
|  3 |     B |
--------------

How to achieve this? using SQL server 2005

Would it be possible for a row with (3, 'C') to exist and would you expect the result to include both (3, 'B') and (3, 'C')? Lieven's answer doesn't handle this case but works correctly with the data currently in your question. — Patrick, Aug 11 '11 at 07:36

Lieven Keersmaekers · Accepted Answer · 2011-08-11T07:36:16.717

6

You can use a regular GROUP BY.

The GROUP BY will

eliminate the NULL value from 1 because other values are present.
retain the NULL value for 2 because it only has NULL values.

SQL Statement

SELECT  id
        , MIN(value)
FROM    YourTable
GROUP BY
        id

Test script

;WITH q (id, value) AS (
    SELECT 1, NULL
    UNION ALL SELECT 1, 'A'
    UNION ALL SELECT 2, NULL
    UNION ALL SELECT 2, NULL
    UNION ALL SELECT 3, 'B'
    UNION ALL SELECT 3, 'B'
    UNION ALL SELECT 3, 'B'
)
SELECT  id
        , MIN(value)
FROM    q       
GROUP BY
        id

edited Aug 11 '11 at 07:36

answered Aug 11 '11 at 07:27

Lieven Keersmaekers

57,207
13
112
146

2

@gvLearner: Use the top script. The bottom script is just a proof-of-concept to show that it will work, with the data contained in the query so that a new table is not needed. – cdhowie Aug 11 '11 at 07:30
@gvLearner - cdhowie is correct. All you need is the sql statement, not the test script. I have altered the statement to better reflect that they are separate (renamed the table). – Lieven Keersmaekers Aug 11 '11 at 07:37
Lets say if I want to add another column referring to another table how can I do? for E.g: Here if I want to add the another field from table desc linking the Id field. – Saanch Aug 11 '11 at 07:46
@gvLearner - Look up the `JOIN` syntax. You can combine two tables by joining them. – Lieven Keersmaekers Aug 11 '11 at 08:20

score 1 · Answer 2 · answered Aug 11 '11 at 07:41

It's a bit convoluted, but it should do the trick:

select distinct x.id, x.value
  from table x
 where x.value is not null 
    or not exists 
       (select y.id 
          from table y 
         where y.id = x.id 
           and y.value is not null)

Select distinct multiple field without NULL

2 Answers2

SQL Statement

Test script