CODEWITHSUNDEEP
sqlsql-server
Murali
Murali

Reputation: 11

SQL Query to display records in a table for duplicate values

I have a table which contain 3 fields RecordID, PhoneNumber,Disposition. I am using SQL server.

Case 1:-

RecordID     PhoneNumber   Disposition

2345         9212345667    PNP
2345         9212345667    PNP
2345         9212345667    PNP 
4563         8234589012    PNP

Here I should display single record from 2 rows. 

Result : 2345       9212345667    PNP
         4563       8234589012    PNP

Only one record should be displayed based on RecordID, PhoneNumber and Disposition duplicates.

Case 2:-

RecordID     PhoneNumber   Disposition

2345         9212345667    PNP
2345         9212345667    CBK
4563         8234589012    PNP

Result : 4563      8234589012    PNP

Here RecordID and PhoneNumber are same but Dispositions are different for first 2 records. So the records should not be displayed.Third record is no where related to duplicate values of RecordID and PhoneNumber. So it should display.

Thanks Please help.

Upvotes: 1

Views: 99

Answers (2)

Serg
Serg

Reputation: 22811

Sql server has Top(1) with ties ... order by plus row_number() 

Select Top(1) with ties RecordID, PhoneNumber, Disposition
from table
-- case 1 
order by row_number() over (partition by RecordID, PhoneNumber, Disposition order by recordID)  
-- case 2 
--order by row_number() over (partition by RecordID, PhoneNumber order by recordID)
;

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

To get each row just once and omit duplicates use distinct

select distinct RecordID, PhoneNumber, Disposition from mytable

To get rows where duplicates don't exist use group by / having count(*) = 1 

select RecordID, PhoneNumber, Disposition from mytable
group by RecordID, PhoneNumber, Disposition 
having count(*) = 1

Upvotes: 2

Related Questions