Group-by in an Oracle database

Question

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...???

Answer 1

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