How to choose a single column?

Question

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

I am 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 am I trying 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

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 

Answer 2

Another option is to use the WITH TIES Row_Number() 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 3

I would use GROUP BY and JOIN . If the COD does not have a NOT NULL , than this should be allowed using the OR in JOIN clause.

  SELECT your_table.* FROM your_table JOIN ( SELECT COD, MAX(value) value FROM your_table GROUP BY COD ) gt ON your_table.COD = gt.COD and (your_table.value = gt.value OR gt.value IS NULL) 

Answer 4

If you can have more than one non-zero value for COD, this will work

 drop table MyTable CREATE TABLE MyTable ( COD INT, ID INT, VALUE VARCHAR(20) ) INSERT INTO MyTable VALUES (28,1, NULL), (28,2,'Supermarket'), (28,3,'School'), (29,1,NULL), (29,2,'School'), (29,3,NULL), (30,1,NULL); WITH Dups AS (SELECT COD FROM MyTable GROUP BY COD HAVING count (*) > 1 ) SELECT MyTable.COD,MyTable.ID,MyTable.VALUE FROM MyTable INNER JOIN dups ON MyTable.COD = Dups.COD WHERE value IS NOT NULL UNION SELECT MyTable.COD,MyTable.ID,MyTable.VALUE FROM MyTable LEFT JOIN dups ON MyTable.COD = Dups.COD WHERE dups.cod IS NULL