Select with duplicate data

[marcelo_oracle]

Select all

CD_AVISO CD_PRESTADOR	CD_CIRURGIA NM_PRESTADOR SN_PRINCIPAL   CD_CIRURGIA_AVISO 
 2959---------435------------------515------------------ANDRE------------------S------------------6116 
 2959---------435------------------2243------------------ANDRE-----------------S------------------6117 
 2959---------435------------------1452------------------ANDRE-----------------S------------------6117 
 2959---------700------------------515------------------RICARDO----------------N------------------6116 
 2959---------700------------------2243------------------RICARDO--------------N------------------6117

I have this table above, the names may have 2 duplicities as above, or 3 or 4 duplicity, but
I need to return only one of each Name, I can not filter because the information is doubled, then always appears two of each. It may be any name the first or second.

See Andre triplicate and duplicate Ricardo. Then I would need:

Select all

2959---------435------------------1452------------------ANDRE-----------------S------------------6117 
2959---------700------------------515------------------RICARDO----------------N------------------6116

can be anyone, but I'm thinking it's not sure ...

[adrianoturbo]

Brother, have you ever wondered how to use the distinct and unique functions in your select?

Select all

 
select distinct xy from tablea;  
 
select unique xy from tablea;  

[marcelo_oracle]

But using distinct the whole line would not have to be the same so that does not occur duplicity ?? There are fields that are different, so the distict will always show the duplicate lines ne ??? Sorry if you misunderstood your solution.

[adrianoturbo]

Truth and makes sense, the distinct orders the resulting lines to identify and eliminate repeated values, but in this case what it seems to me is that he wants to return only once the name of the provider, I imagine that the foreign key (I do not know this is a Domain table) Be the CD_Prestator, but it performed two types of different surgeries and ended up generating two different records and without duplicity.

[Lucas PM Carvalho]

Good morning,

If you cluster the data using the "Group by" would not solve the problem?

falou Hugs.

[stcoutinho]

Marcelo,

Could you describe with examples what do you need?

Type .. Mount a mass with 2 doublets to 4 duplicities, and show us the results that need to appear? Maybe with this example, the staff feel more comfortable to give suggestions.

Hugs,

Sergio Coutinho

[Jota]

Marcelo, I saw that your problem is CD_Cyurgy that is different, if using distinct will not advance because of this field.

But if you use Min or Max and do this can help you:

Select all

 
SELECT CD_AVISO, 
            CD_PRESTADOR, 
            MIN(CD_CIRURGIA), 
            NM_PRESTADOR,  
            SN_PRINCIPAL,  
            CD_CIRURGIA_AVISO 
  FROM TABELA_DO_MARCELO 
GROUP BY CD_AVISO, 
                CD_PRESTADOR,  
                 NM_PRESTADOR,  
                 SN_PRINCIPAL,  
                 CD_CIRURGIA_AVISO 

[gpereira]

In the case of Group BY with min in the "CD_Cyurgy" still maintained duplicate name by "cd_cyrurgery_aviso".

Particularly, I like to perform deduplication via Over Partition.

Ex:

Select all

SELECT CD_AVISO, 
            CD_PRESTADOR, 
            CD_CIRURGIA, 
            NM_PRESTADOR,  
            SN_PRINCIPAL,  
            CD_CIRURGIA_AVISO FROM ( 
 
SELECT CD_AVISO, 
            CD_PRESTADOR, 
            CD_CIRURGIA, 
            NM_PRESTADOR,  
            SN_PRINCIPAL,  
            CD_CIRURGIA_AVISO, 
-- PARTICIONANDO NOME POR ULTIMO CD_CIRURGIA E ULTIMO CD_CIRURGIA_AVISO 
            ROW_NUMBER() 
            OVER (PARTITION BY NM_PRESTADOR ORDER BY CD_CIRURGIA  DESC, CD_CIRURGIA_AVISO DESC) AS ORD) 
WHERE ORD = 1

[douglasmattos]

Brother,

This error occurs because it has more than one provider linked to surgery. I already had the same problem. (From what I am seeing you use the right MV system?