Remove duplicate row based on specific column value

Question

I have a table data like

+--------------------------------------+--------------------------------------+-----------+--------+
|            conversationid            |            participantid             | mediatype | rownum |
+--------------------------------------+--------------------------------------+-----------+--------+
| 01fda91b-6001-4904-b0bc-8c61aec654b4 | 13f954cb-4acb-4ab9-89e7-c48ece23a043 | callback  |      1 |
| 01fda91b-6001-4904-b0bc-8c61aec654b4 | 13f954cb-4acb-4ab9-89e7-c48ece23a043 | voice     |      2 |
| 0519386a-2b8f-403c-b65b-fd8cc3c09a32 | b033fe2f-a58c-4973-8006-54561b5a5bf7 | voice     |      1 |
| 085adea7-1deb-45d8-ae61-639255a689ce | 4151d364-5740-4dcf-b756-9772efaacb26 | voice     |      1 |
| 0c50e9c5-cbe0-4da5-9a8c-976efea255b2 | 8f1ee999-8454-4db9-8c24-68e773350f39 | callback  |      1 |
| 138da3c8-c118-4ddf-b294-57301261eb97 | cf2b643e-b07f-46c8-a52c-0cb5492e6485 | voice     |      1 |
| 613c51c6-3c8b-4b53-91de-de3cc004fa92 | 54a84468-e452-4820-9c8a-89904ff97d8d | callback  |      1 |
| 613c51c6-3c8b-4b53-91de-de3cc004fa92 | 54a84468-e452-4820-9c8a-89904ff97d8d | voice     |      2 |
+--------------------------------------+--------------------------------------+-----------+--------+  

I'm trying to remove the duplicate data based on specific column value (mediatype).

I need to exclude the rows with meditype "voice" only when there is two value (voice,Callback) for conversationid and participationid combo.

I tried the code

;with cte as(select conversationid,participantid ,
mediatype,row_number() over (partition by conversationid,participantid order by mediatype ) 
as rownum from #temp
)select * from cte where rownum=1 
order by conversationid,participantid ,mediatype

But the result is not based on specific value but it excludes based on alphabets. I need a condition where the row should be excluded when duplicate row has mediatype as voice. for other unique rows when there is no callback value, it should return mediatype voice.

Answer 1

You want all the rows with mediatype = 'callback' or mediatype = 'voice' if there is not also mediatype = 'callback'.
Apply these conditions in the WHERE clause:

select * from tablename t
where 
  mediatype = 'callback'
  or 
  not exists (
    select 1 from tablename
    where conversationid = t.conversationid and participantid = t.participantid
          and mediatype <> t.mediatype
  ) 

See the demo.
Results:

> conversationid                       | participantid                        | mediatype | rownum
> :----------------------------------- | :----------------------------------- | :-------- | -----:
> 01fda91b-6001-4904-b0bc-8c61aec654b4 | 13f954cb-4acb-4ab9-89e7-c48ece23a043 | callback  |      1
> 0519386a-2b8f-403c-b65b-fd8cc3c09a32 | b033fe2f-a58c-4973-8006-54561b5a5bf7 | voice     |      1
> 085adea7-1deb-45d8-ae61-639255a689ce | 4151d364-5740-4dcf-b756-9772efaacb26 | voice     |      1
> 0c50e9c5-cbe0-4da5-9a8c-976efea255b2 | 8f1ee999-8454-4db9-8c24-68e773350f39 | callback  |      1
> 138da3c8-c118-4ddf-b294-57301261eb97 | cf2b643e-b07f-46c8-a52c-0cb5492e6485 | voice     |      1
> 613c51c6-3c8b-4b53-91de-de3cc004fa92 | 54a84468-e452-4820-9c8a-89904ff97d8d | callback  |      1