How can I select distinct by one column?

Question

I have a table with the columns below, and I need to get the values if COD is duplicated, get the non NULL on VALUE column. If is not duplicated, it can get a NULL VALUE. Like the example:

I'm using SQL SERVER.

This is what I get:

COD ID  VALUE
28  1   NULL
28  2   Supermarket
29  1   NULL
29  2   School
29  3   NULL
30  1   NULL

This is what I want:

COD ID  VALUE
28  2   Supermarket
29  2   School
30  1   NULL

What I'm tryin' to do:

;with A as (    
(select DISTINCT COD,ID,VALUE from CodId where ID = 2)  
UNION   
(select DISTINCT COD,ID,NULL from CodId where ID != 2) 
)select * from A order by COD

Answer 1

Another option is to use the WITH TIES clause in concert with Row_Number()

Example

Select top 1 with ties * 
 from  YourTable
 Order By Row_Number() over (Partition By [COD] order by Value Desc)

Returns

COD ID  VALUE
28  2   Supermarket
29  2   School
30  1   NULL

Answer 2

You can try this.

DECLARE @T TABLE (COD INT, ID INT,  VALUE VARCHAR(20))
INSERT INTO @T 

VALUES(28,  1,   NULL),
(28,  2   ,'Supermarket'),
(29,  1   ,NULL),
(29,  2   ,'School'),
(29,  3   ,NULL),
(30,  1   ,NULL)


;WITH CTE AS (
SELECT *, RN= ROW_NUMBER() OVER (PARTITION BY COD ORDER BY VALUE DESC) FROM @T
)
SELECT COD, ID ,VALUE  FROM CTE
WHERE RN = 1

Result:

COD         ID          VALUE
----------- ----------- --------------------
28          2           Supermarket
29          2           School
30          1           NULL