0

on an Oracle database. If I make an:

select id,id_operator,denumire,cod_fiscal,loc_den,str
 from rap where is_punct_lucru=1
 and id_operator in (
    select id_operator from rap 
        where is_punct_lucru=1
        group by id_operator,denumire,loc_den,str having count(*) > 1
    )
order by id_operator

I get:

ID      ID_OPERATOR     DENUMIRE            COD_FISCAL      LOC_DEN                 STR 
70446   762200          SC AF SRL           xxx-453502      Alba Iulia              SOSEAUA DE CENTURA
70430   762200          SC AF SRL           xxx-453449      Alba Iulia              SOSEAUA DE CENTURA
70445   762200          SC AF SRL - OCNA    xxx-379128      Ocna                    MIHAIL EMINESCU 

but if I "shoot" just the

select id_operator,count(*) from rap 
        where is_punct_lucru=1
        group by id_operator,denumire,loc_den,str having count(*) > 1

it gives me just 2 rows on 762200 because of the LOC_DEN and STR. How can I include the ID in the group by without loosing the rows...???

0

ok, I solved this by:

select * from 
(
    select id,id_operator,denumire,loc_den,str,
           row_number() over (partition by 
                     id_operator,loc_den,str order by id_operator) as no
    from rap
    where is_punct_lucru=1
    order by id_operator
)
where no>1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.