CODEWITHSUNDEEP
sqlsql-servert-sqlnulldistinct
Falakienos
Falakienos

Reputation: 107

How can I select distinct by one column?

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

Upvotes: 6

Views: 6021

Answers (4)

RegBes
RegBes

Reputation: 569

If you may have more than one non null value for a 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

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I would use GROUP BY and JOIN. If there is no NOT NULL value for a COD than it should be resolved 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)

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 6

Serkan Arslan
Serkan Arslan

Reputation: 13393

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

Upvotes: 9

Related Questions