CODEWITHSUNDEEP
sqlsql-server
absolute333
absolute333

Reputation: 81

Find rows with column equal and other different

I need to extract all rows that have same column value (wihout duplicates) and the other column value with different value.

I have problems when I'm trying to get null values since the sentence "having count (distinct)" not include null values.

My example (https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=ae1db1de07f9a54171c5121b49112273) :

create table definition
(
   job varchar(256),
   bat varchar(256),
   descr varchar(256)
);

INSERT INTO definition (job,bat,descr)
VALUES
   ('JOBA','SCRIPTA','caracoles'),
   ('JOBA','SCRIPTA','navajas'),
   ('JOBB','SCRIPTB','navajas'),
   ('JOBA','SCRIPTC','casa'),
      ('JOBC','SCRIPTC','asombrado'),
            ('JOBC','SCRIPTC','casa'),
      ('JOBC','SCRIPTKKK','asombrado'),
      ('JOBC','SCRIPTCDSFDF','pepelu'),
      ('JOBD',NULL,'casa'),
      ('JOBD','DFDSFDSFDSF','caracoles'),
   ('JOBA','SCRIPTC','casa'),
   ('JOBA','SCRIPTC','casa'),
   ('JOBJ','SCRIPTC','casa'),
   ('JOBB','SCRIPTB','love');

SELECT DISTINCT d.*
FROM definition d
JOIN
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(bat)) > 1
) x
ON d.job = x.job

It gives me:

job     bat
JOBA    SCRIPTA
JOBA    SCRIPTC
JOBC    SCRIPTC
JOBC    SCRIPTCDSFDF
JOBC    SCRIPTKKK
Warning: Null value is eliminated by an aggregate or other SET operation.

I need also 

JOBD,NULL
JOBD,DFDSFDSFDSF

Upvotes: 0

Views: 69

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I recommend exists:

select d.*
from definition d
where  exists (select 1
               from definition d2
               where d2.job = d.job and
                     d2.bat is distinct from d.bat
              );

Here is a db<>fiddle.

In addition, this can make use of an index on definition(job, bat) and with the index should be faster than a query that has aggregation.

Upvotes: 0

George Joseph
George Joseph

Reputation: 5932

Since you are using postgres the syntax is slightly different

SELECT DISTINCT d.job,d.bat
FROM definition d
JOIN
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(case when bat is null then '' else bat end)) > 1
) x
ON d.job = x.job
order by 1

https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=27b4cf8e44d7a088331f70ba2b519644

Upvotes: 1

David
David

Reputation: 474

You should include NULL value as a valid value of bat field in the select sentence.

This will solve your problem.

SELECT DISTINCT d.*
FROM definition d
JOIN
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(isnull(bat, 'N/A'))) > 1
) x
ON d.job = x.job

Upvotes: 1

Related Questions