CODEWITHSUNDEEP
sqlsql-server
Thierry
Thierry

Reputation: 6478

Find duplicates where another column have different columns

I need to update records that will match a specific query, so I'm currently trying to figure out how to find a list of duplicate values where one column differs in value.

I have the following table definition

DocumentId (BIGINT)
NotePopupId (INT)
IsPopup (BIT)
Note (NVARCHAR(100))

My table might have data as follows:

1|1|False|Note1

1|2|False|Note2

2|1|False|Note1

2|2|True|Popup1

3|1|False|Note1

3|2|True|Popup1

4|1|False|Note1

4|2|False|Note2

I need to return a list of DocumentId that have more than one DocumentId defined but where The IsPopup field is True and False and ignore the ones where they are all false or all true.

I understand how to write a basic query that will return the total number of duplicates but I don't get how would I ensure that it will only returns the duplicates that have their IsPopup field set to true and false for 2 or more records with the same DocumentId.

So in this instance, based on the above, it would return DocumentId 2 and 3.

Thanks.

Upvotes: 0

Views: 111

Answers (4)

Dudi Konfino
Dudi Konfino

Reputation: 1136

SELECT  documentId
    from table
    group by documentId
     having min(convert(int,isPopup)) !=  max(convert(int,isPopup));

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

This might be a little more efficient 

select distinct t1.documentId
  from table t1 
  join table t2
    on t1.documentId = t2.documentId 
   and t1.IsPopup = 'true'
   and t2.IsPopup = 'false'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I am inclined to handle a question like this using group by and aggregation:

select documentId
from table
group by documentId
having min(cast(isPopup as int)) = 0 and max(cast(isPopup as int)) = 1;

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93754

Find Distinct Count and filter the group's whose count is more than 1. Try this.

select DocumentId 
from yourtable 
group by DocumentId 
having count(Distinct IsPopup)>1

If you want to return documentId when there is only one IsPopup then use this

select DocumentId 
from yourtable 
group by DocumentId 
having count(Distinct IsPopup)>1 or count(IsPopup)=1

Upvotes: 2

Related Questions